1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Macros: unable to write a loop in a funtion..

Discussion in 'Business Applications' started by sk0101, Apr 15, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. sk0101

    sk0101 Thread Starter

    Joined:
    Nov 8, 2007
    Messages:
    277
    Hi,

    I want to include a funtion in Auto_Open()..that would do the following steps:

    read each row in a "Data Tab" and do a condition and return the data to a different tab.

    I am not sure how to do a Loop condition:

    ---
    Dim wk As Excel.Workbook
    Set wk = Application.ActiveWorkbook

    i = 3
    Do
    //the loop will run until the cell in the Data Tab contains data
    //..it will start to check B3 than B4..etc...until the B? will be empty
    If (wk.Worksheets("NewTab")...
    I will call a funtion Populate().. I made it work.. I would need to pass a parameter
    ie. (B&i) <-- the cell it is currently looking at, which is not empty.

    I hope it makes sense what I am trying to do..

    Thank you for help..
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Can you post a sample workbook so we can see the data layout? If you are not comfortable posting it you can also email to me. Just replace any sensitive data with dummy info as needed.

    Regards,
    Rollin
     
  3. sk0101

    sk0101 Thread Starter

    Joined:
    Nov 8, 2007
    Messages:
    277
    Hi Rollin,

    Please see my attached file.. I had added comments in the vba file.

    I hope it is clean,

    Thank you for your help,
     

    Attached Files:

  4. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    If you are starting in cell B3 and want to loop until the last cell in the column containing data you can use the loop below. It assumes that there are no blank rows of data in the column between records.

    Code:
    For i = 3 to Cells(Rows.Count,"B").End(xlUp).Row
    
    If Range("B" & i).Value = "A" _
    or Range("B" & i).Value = "B" _
    and Range("C" & i).Value = "M" Then
    Call Populate("B" & i)
    End If
    
    Next i
    Regards,
    Rollin
     
  5. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Try this updated code

    Code:
    Sub TransferValues()
    
    For i = 3 To Sheets("Data").Cells(Rows.Count, "B").End(xlUp).Row
    
    If UCase(Sheets("Data").Range("B" & i).Value) = "A" _
    Or UCase(Sheets("Data").Range("B" & i).Value) = "B" _
    And UCase(Sheets("Data").Range("C" & i).Value) = "M" Then
    
    Sheets("NewData").Range("A" & Sheets("NewData").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row).Value = _
    Sheets("Data").Range("B" & i).Value & ", " & Sheets("Data").Range("C" & i).Value
    Sheets("NewData").Range("A" & Sheets("NewData").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row).Value = Sheets("Data").Range("D" & i).Value
    
    End If
    
    Next i
    
    End Sub
    
    Regards,
    Rollin
     
  6. sk0101

    sk0101 Thread Starter

    Joined:
    Nov 8, 2007
    Messages:
    277
    Thanks alot.. It works only if I execute the function from Data Tab.. If I execute from NewData Tab, nothing happens, I am assuming because I specified
    Dim wk As Excel.Workbook
    Set wk = Application.ActiveWorkbook
    so it looks at the currect tab I am in.

    I tried to update it to:
    Set wk = Worksheets("Data")

    so no-matter what Tab I am currectly on, it will know to take the data from Data Tab. But it failed.. is there a different way to specify?

    Thanks
     
  7. sk0101

    sk0101 Thread Starter

    Joined:
    Nov 8, 2007
    Messages:
    277
    oopps just saw your updated code.. let me check!!
    sorry
     
  8. sk0101

    sk0101 Thread Starter

    Joined:
    Nov 8, 2007
    Messages:
    277
    Thank u, the code works perfectly.

    Want to understand how the Line2 works. Please explain me the bold part

    Line 1: Sheets("NewData").Range("A" &
    Line 2: Sheets("NewData").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row).Value

    Thank you
     
  9. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Rows.Count will give you the total number of available rows in the worksheet.

    When you say Cells(Rows.Count, "A") you are telling the application to start with the last cell in column A
    (row 65536 for versions prior to Office 2007)

    The .End(xlUp) portion tells it to find the next non-blank cell above (xlUp) that starting cell. The application
    understand this to be the last record in the column (You can also use xlDown to get first non blank cell from top or
    xltoRight, xltoLeft to get the first/last non blank cell across the row)

    The .Offset(1,0) portion tells the application to drop down 1 row and 0 columns from this last record that was found.
    (You could also use offset(0,1) to move one column to the right instead of 1 row down, you can also use negative numbers)

    and finally the .Row gives you the row number of this first blank cell beneath the last record in column A.

    Does that make sense?

    Regards,
    Rollin
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/704031

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice