Need help with VB code

wilkisa

Well-known Member
Joined
Apr 7, 2002
Messages
657
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
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.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
Thanks, Al. Yes, seems obvious once you explained it! (Kept thinking that we were at the top of the column).
 
Upvote 0
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!!!
 
Upvote 0
Bless you, Al. Where should I send it? I have been unable to find a way to attach it to this site.

:wink:
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top