Need help with VB code
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Need help with VB code

  1. #1
    Board Regular wilkisa's Avatar
    Join Date
    Apr 2002
    Location
    Decatur IL, USA
    Posts
    632
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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.

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    Board Regular wilkisa's Avatar
    Join Date
    Apr 2002
    Location
    Decatur IL, USA
    Posts
    632
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  8. #8
    Board Regular wilkisa's Avatar
    Join Date
    Apr 2002
    Location
    Decatur IL, USA
    Posts
    632
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!!!

  9. #9
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Send me your workbook.

  10. #10
    Board Regular wilkisa's Avatar
    Join Date
    Apr 2002
    Location
    Decatur IL, USA
    Posts
    632
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com