Loop / Do problem for Newbie

[cagsy]

Hi Guys,

I'm pretty average in VBA as is evidenced by this question however hopefully this will be a piece of cake for someone. I had a rough idea of how to do it and did have a go, however jumping from worksheet to worksheet seemed to make the loop lose focus and I couldn't figure out the active cell offset etc. Ended up as a mess basically

I have one Excel workbook with 2 worksheets "Input" & "Data".

Worksheet "Input" is a sheet with a number of specific cell values to be used in a macro (which already exists and works fine). Two of the cell values F2 and F8 need to be updated before a macro is run. At this stage this data is being entered manually. All other values are fixed.

Worksheet "Data" is the data source and consists of 2 columns an ID (col A) & a volume for that ID (col B). This could have anywhere from 4 to 200 values down the page. There are no column headings.

I need to take the first 2 cell values A1 and B1 from "Data" worksheet and paste A1 into F2 of "Input" sheet and B1 into F8 of "Input" sheet
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
After the data is populated I need to call up a macro lets call it "calculate"
<o> </o>
Once the macro successfully runs and closes I want to clear cell F2 and F8 contents. I need to then return to the "Data" worksheet and repeat the same process for the next row of cells A2, B2 then A3, B3 etc until I reach a blank cell.

Any ideas / ways forward would be very much appreciated

Many thanks
Andrew

 

[Marcelo Branco]

Hi,

Welcome to the forum

Maybe something like (not fully tested)
Try it on a test-workbook

Sub test()
    Dim wkData As Worksheet, wkInput As Worksheet
    Dim lastRow As Long, i As Long
    
    Set wkData = Sheets("Data")
    Set wkInput = Sheets("Input")
    
    With wkData
        
        'Getting the last row with data
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        
        'Looping through row 1 till lastRow
        For i = 1 To lastRow
            wkInput.Range("F2") = .Range("A" & i)
            wkInput.Range("F8") = .Range("A" & i).Offset(, 1)
            'Call Calculate '<--Uncomment this line to call your macro
        Next i
        
        wkInput.Range("F2") = ""
        wkInput.Range("F8") = ""
        
    End With
    
End Sub

HTH

M.

 

[cagsy]

Hi,

Welcome to the forum

Maybe something like (not fully tested)
Try it on a test-workbook

Sub test()
    Dim wkData As Worksheet, wkInput As Worksheet
    Dim lastRow As Long, i As Long
    
    Set wkData = Sheets("Data")
    Set wkInput = Sheets("Input")
    
    With wkData
        
        'Getting the last row with data
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        
        'Looping through row 1 till lastRow
        For i = 1 To lastRow
            wkInput.Range("F2") = .Range("A" & i)
            wkInput.Range("F8") = .Range("A" & i).Offset(, 1)
            'Call Calculate '<--Uncomment this line to call your macro
        Next i
        
        wkInput.Range("F2") = ""
        wkInput.Range("F8") = ""
        
    End With
    
End Sub

HTH

M.

Hey Marcelo,

thanks very much for such a quick reply. I ran it through and it works exactly as required. That little bit of code will come in quite handy for a bunch of other things I had planned

All the best

cheers
Andrew

 

[Marcelo Branco]

Hey Marcelo,

thanks very much for such a quick reply. I ran it through and it works exactly as required. That little bit of code will come in quite handy for a bunch of other things I had planned

All the best

cheers
Andrew

Andrew,

You are welcome. Glad to help

M.