Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2006
    Posts
    67

    Unanswered: Easy VBA code explaination.

    Sub LoopRange1()

    'Have x start at row2
    x = 2

    'Loop until a blank row is found
    Do While Cells(x, 2).Value <> ""
    'This will put the values of the
    'first and second column (A and B)
    'together with a space between them
    'in the third column (C)
    Cells(x, 3).Value = Cells(x, 1).Value + _
    " " + Cells(x, 2).Value

    'increase the value of x by 1
    'to act on the next row
    x = x + 1
    Loop

    Here is an example I copied off a site, (I did a little modification, because my column and row are different) it ran perfectly, I was wondering if someone could explain some of the code lines.

    This macro is used to combine info in column A and B together, and have it show in column C.

    I do not understand what exactly is Cells(x,2) or whatever. It says the x keeps track of the current row, what does the 2 mean?

    I highlighted a line in the codes, could someone explain it to me ... ?

    thanks in advance.

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy. There are at least two ways to refer to cells. The most common in VBA is Range("B2"). However, Cells(2,2) is often more useful, especially when looping through the cells.

    The first number is the row index, the second is column. So in your example, Cells(x,2) means go through all rows in the range (based on variable x), and do it with Column B. Thus,

    the first part is column C (3), and it will add the values in columns A (1) and B (2).
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Nov 2006
    Posts
    67
    Quote Originally Posted by shades
    Howdy. There are at least two ways to refer to cells. The most common in VBA is Range("B2"). However, Cells(2,2) is often more useful, especially when looping through the cells.

    The first number is the row index, the second is column. So in your example, Cells(x,2) means go through all rows in the range (based on variable x), and do it with Column B. Thus,

    the first part is column C (3), and it will add the values in columns A (1) and B (2).
    Thanks for the explanation...i get it now

  4. #4
    Join Date
    Nov 2006
    Posts
    67
    Using that exact concept. How do I combine information in 3 columns together? Like merging all information in all 3 columns into 1 column. There will be blanks between rows. But that shouldnt matter I hope.

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    Code:
    Cells(x, 4).Value = Cells(x, 1).Value & " " & _
      Cells(x, 2).Value & " " & Cells(x, 3).Value
    This is concatenating the values (don't use + for that) from Col A, B, and C and putting the result in Col. D. If you don't want spaces, then take out this portion in each section: & " "

  6. #6
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by tialongz
    Using that exact concept. How do I combine information in 3 columns together? Like merging all information in all 3 columns into 1 column.
    You will need to change this line to include another column. The Cells method of referring to a cell uses 2 values, a row value (uses the variable 'x' in your example) and a column value. If you wanted to concatenate the Columns 1,2, and 3 into the 1st column you should change this up a little.
    Original:
    Cells(x, 3).Value = Cells(x, 1).Value + _
    " " + Cells(x, 2).Value


    Modified
    Cells(x,1) = Cells(x,1) & Cells(x,2) & Cells(x,3)

    In this case we're a combining columns 1,2 and 3. Into column 1. To keep things a little clearer you could use variable to hold the value then replace the value of column 1 with the variable value like this.

    strValue = Cells(x,1) & Cells(x,2) & Cells(x,3)
    Cells(x,1) = strValue



    Quote Originally Posted by tialongz
    There will be blanks between rows. But that shouldnt matter I hope.
    In fact it does matter. This line 'Do While Cells(x, 2).Value <> ""

    Says start a loop and continue while the cell in the variable row and column 2 are NOT blank. So as soon as you have a blank row your loop will end. You will have to absolutly identify the last used row and use a Looping control structure besides Do While if you know there will be blank rows in your data.

    Shades, you were quicker than I was with a response.
    ~

    Bill

  7. #7
    Join Date
    Mar 2006
    Posts
    163
    There's actually no need for this loop.
    2 columns:
    Code:
    Dim LastRow As Long
    
    LastRow = Range("B" & Rows.Count).End(xlUp).Row
    
    With Range("C2:C" & LastRow)
         .Formula = "=A2 & CHAR(32) & B2"
         .Copy
         .PasteSpecial xlPasteValues
    End With
    Application.CutCopyMode = False
    3 columns:
    Code:
    Dim LastRow As Long
    
    LastRow = Range("B" & Rows.Count).End(xlUp).Row
    
    With Range("D2:D" & LastRow)
         .Formula = "=A2 & CHAR(32) & B2 & CHAR(32) & C2"
         .Copy
         .PasteSpecial xlPasteValues
    End With
    Application.CutCopyMode = False

  8. #8
    Join Date
    Oct 2003
    Posts
    1,091
    I tend to avoid Do ... While loops.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  9. #9
    Join Date
    Nov 2006
    Posts
    67
    Thank you all for the codes...now I have to make it work somehow.

Posting Permissions

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