Need help with VB code

[wilkisa]

I am not sure how to begin but what I need is code that will compare the contents of two cells in one row. If the cells do not match, it will insert blank cells (not rows), moving the rightmost data down. Then it will begin the process of comparison again. I have a sample sheet that I would be happy to send to someone for testing.

[Al Chara]

Not sure if it is what you want, but try the following code:

Do
If [a3] <> [b3] Then [b1].Insert Shift:=xlDown
Loop Until [a3] = [b3]

The code will test cells a3 and b3 if they are not the same then it will insert a blank cell in b1 pushing the data down. The code will stop when a3=b3. I also recommend adding in a counter so the code doesn't loop forever.

Dim counter As Integer
Do
If [a3] <> [b3] Then [b1].Insert Shift:=xlDown
counter = counter + 1
Loop Until [a3] = [b3] Or counter = 10


_________________
Hope this helps.
Kind regards, Al.

[ This Message was edited by: Al Chara on 2002-04-09 07:44 ]

[wilkisa]

Thanks, Al. Now how can I run this until the end of the data list?

On 2002-04-09 07:42, Al Chara wrote:
Not sure if it is what you want, but try the following code:

Do
If [a3] <> [b3] Then [b1].Insert Shift:=xlDown
Loop Until [a3] = [b3]

The code will test cells a3 and b3 if they are not the same then it will insert a blank cell in b1 pushing the data down. The code will stop when a3=b3. I also recommend adding in a counter so the code doesn't loop forever.

Dim counter As Integer
Do
If [a3] <> [b3] Then [b1].Insert Shift:=xlDown
counter = counter + 1
Loop Until [a3] = [b3] Or counter = 10


_________________
Hope this helps.
Kind regards, Al.

[ This Message was edited by: Al Chara on 2002-04-09 07:44 ]

[Al Chara]

You can set your counter equal to the number of cells that you want to do:

counter = Intersect(Columns("B"), ActiveSheet.UsedRange).Rows.Count

Or use the following:

Do
If [a3] <> [b3] Then [b1].Insert Shift:=xlDown
Loop Until [a3] = [b3] Or [b3] = ""

This loops until cell b3 is empty.

_________________
Hope this helps.
Kind regards, Al.

[ This Message was edited by: Al Chara on 2002-04-09 07:53 ]

[sjk]

Al: This is just a question to help me learn since I am new to VBA and attempting to learn. The macro language you described above: it seems to me that cell A3 will never = B3 since the "B" column of data is being moved down each time. Unless there is an intermediate step where a user is entering new data into the cell B3, I am not sure conceptually how this would work. Perhaps you or Wilkisa could help. Thanks, SJK.

[Al Chara]

sjk,

I assuming that there is data in the cells above. So everytime the macro loops it tests the value that was in the cell above. It inserts a blank cell at the top of the column, which pushes the cells down. To make this work properly, A3 and B3 should be changed to the cells at the bottom of the column. Does this answer your question?

[sjk]

Thanks, Al. Yes, seems obvious once you explained it! (Kept thinking that we were at the top of the column).

[wilkisa]

Al, I am still very unclear on how to make this work. I have a table of static data, col. A-E and rows 2-168. Row A is a shipping number. Now in col. G-K I have 59 rows of data beginning in row 2 also. Col. G is the shipping number. What I need is to compare G2 to A2. If G2 <> A2, then insert blank cells in G2-K2, move the data down and compare now G3 to A3. If G3=A3, then leave it alone, move cursor to G4 and start comparing again until all of the data in G-K has been placed in the correct row to correspond with the data in Col. A. Does this make sense?

I am very green with VB and need some step by step handholding.

Thanks!!!

[Al Chara]

Send me your workbook.

[wilkisa]

Bless you, Al. Where should I send it? I have been unable to find a way to attach it to this site.