Help on Excel Macro

[ROSER72]

Hi,

I need to create a VB macro that inserts cells in a column until the value of the column cell matches the value of another cell and I don't know where to start.

A B
1 1
2 2
3 4
4 5
5
In the example the macro should read the value of column B and check if it's the same as column A. If they match the macro should read the next value in column B. When the macro reaches 4 in column b the macro shoud add a cell and shitft the cells down until the value of column b 4 matches the value in column a.

Any help appreciated

Roser72

 

[AnotherHiggins]

What if you have a number in column B that isn't in column A?

Is column A always sequential numbers? If so, then you are basically just trying to get each number in column B on the corresponding row. That means you don't have to worry about my first question.

Basically, you'll want to loop through column B and compare the value with either the value in column A or just the row number.

But what's the business case for this? What is your ultimate goal in this?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Bong]

Have you tried recording a macro doing what you describe (which is what I would do to get started)? What is it you don't know how to do (matching || shifting || inserting|| ?)?

_________________
Bob Rashkin

 

[ROSER72]

I don't know how to do the matching!

 

[ROSER72]

I've made a start on how to loop on a range:

Sub ShiftCells()

Range("G5").Select
Do Until ActiveCell.Value = "END"
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Now I need to know how to do the matching and second offset the other column

 

[SkipVought]

You want to have a value in every row in colum B that matched the value in columnm A, correct?

Copy column A

Paste in column B.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[ROSER72]

No I want to lineup the values in column b to match the values in column a. If the values does exists in column a the the cells must shift down until they match

1 1
2 3
3 6
4 10
5
6
7
8
9

the relust should be

1 1
2
3 3
4
5
6 6
7
8
9 9

 

[AnotherHiggins]

You still haven't answered my first question from the first reply in this post - and you just accidentally did it in your last post!

You listed:[tt]
1 1
2 3
3 6
4 [red]10[/red]
5
6
7
8
9[/tt]

What would you want to happen here?! There is no 10 in Column A.

Honestly, if you expect us to take our time to answer your questions, you should really answer ours. We are trying to help you, after all!


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[ck1999]

Someone code throw him a little hint

dim vrow
dim counter
vrow=range("a1").end(xldown).row
for counter =1 to vrow
   ' in here do your comparison and insert row down

next vrow

ck1999

 

[combo]

Is it always a sequence 1, 2, 3... in A and positive integer in B? If so:

Set rng1 = Range("A1:A9")
vMax = 9
Set rngIn = Range("B1:B4")
vIn = rngIn
rngIn.ClearContents
For Each v In vIn
    If v <= vMax Then rng1.Cells(1, 1).Offset(v - 1, 1) = v
Next v

combo

 

[AnotherHiggins]

We can give him lots of code. Here's some:

Sub LineUpVals()
    LstRow = Range("B" & ActiveSheet.Rows.Count).End(xlUp).Row

    For each c In Range("B1:B" & LstRow)
        If c.value <> c.offset(,-1).value Then c.Insert shift:=xlDown
    Next c
End Sub

But that doesn't account for any variability in the data.

What if there's a number in column B that isn't in column A?
What if column B isn't sorted before running code?.

All I'm asking for is clarification on what he wants to do and why.

And, honestly, I doubt that this is even the best way to go about doing whatever he is after. So if we find that out, maybe someone here can suggest a better solution.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[ROSER72]

OK some answers to your questions. I will always sort both columns before starting, and yes it could be that the data in column be b could not be present in column A but I thought that to start with I would make it easy. Also in my last example 10 should not be there. My final objective it to sort both columns so that I can have them ready to use with another aplication. I'll to set another example and this should be the good one:

Column A Column B
1 1
3 2
4 3
5 5
7 6
8 9

After running the macro the columns should be sort as follows:

Column A Column B
1 1
2
3 3
4
5 5
6
7
8
9

This is very common in my daily business where customer keep on changing you requirements and you have already started your programming works. Now imagine either list over 500 lines; you end up going nuts!

Thanks in advance to everybody
Roser72 (obviuosly newbie)

 

[SkipVought]

You can use MS Query OUTER JOIN. faq68-5829.

Completed in about 5 minutes...

SELECT `A$`.`Column A`, `B$`.`Column B`
FROM {oj `B$` `B$` LEFT OUTER JOIN `A$` `A$` ON `B$`.`Column B` = `A$`.`Column A`}

UNION

SELECT `A$`.`Column A`, `B$`.`Column B`
FROM {oj `A$` `A$` LEFT OUTER JOIN `B$` `B$` ON `B$`.`Column B` = `A$`.`Column A`}

results
[tt]
Column A Column B
2
6
9
1 1
3 3
4
5 5
7
8
[/tt]
CAVEAT:
Each of these TABLES must be on separate sheets


Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[ck1999]

A solution using vba maybe

Dim counter

counter = 2 ' Start in row 2
While Cells(counter, 1) <> "" Or Cells(counter, 2) <> ""
  If Cells(counter, 1) <> Cells(counter, 2) Then
     If Cells(counter, 1) > Cells(counter, 2) Then
          Cells(counter, 1).Insert shift:=xlDown
     Else
          Cells(counter, 2).Insert shift:=xlDown
     End If
  End If
  counter = counter + 1
Wend

[code]

I tested this code and it works for me.  

ck1999

 

[ROSER72]

Thanks for your codes, and your time