Copy cells from 1 sheet to another in a loop using a different starting and ending position

[Learner99]

Hi could you please help me with this loop.

I’m trying to copy all cells from my current sheet starting at “B9” down.
To a New sheet Starting at cell “A34”

I marked out the 2 areas where I’m stuck.

Thanks

    Range("B9").Select  ‘ starting point
    Dim x As Variant
    Dim Icount As Integer
  
    
    For Each x In Range("B9", Range("B9").End(xlDown))   'For Loop searches column for a blank
    If x = "" Then Exit For                              'Ends the loop when it hits a blank
   
    vpart2 = Range("B" & Icount).Value   ‘*******ß---Need Help Here
    vcomplete = "SET datafile=" & Chr(34) & vpart1 & vpart2 & ".dat" & Chr(34)
    Sheets("Output").Range("A" & Icount).Value = vcomplete     ‘*******ß---Need Help Here
   
    Icount = Icount + 1
    Next

 

[mhillmann]

It looks like you are trying to reference a cell with the lines you need help with.

Try using the Cells(rowIndex, colIndex) call.

Like this:

vpart2 = Cells(Icount, "B").Value   '*******ß---Need Help Here
    vcomplete = "SET datafile=" & Chr(34) & vpart1 & vpart2 & ".dat" & Chr(34)
    Sheets("Output").Cells(Icount, "A").Value = vcomplete     '*******ß---Need Help Here

 

[Learner99]

It looks like you are trying to reference a cell with the lines you need help with.

Try using the Cells(rowIndex, colIndex) call.

Like this:

vpart2 = Cells(Icount, "B").Value   '*******ß---Need Help Here
    vcomplete = "SET datafile=" & Chr(34) & vpart1 & vpart2 & ".dat" & Chr(34)
    Sheets("Output").Cells(Icount, "A").Value = vcomplete     '*******ß---Need Help Here

This makes sense for the start position B9 , but it does not set the start position to copy the data to on the next sheet which needs to be A34

 

[mhillmann]

If it just that you could offset it with a "+ 25".

Like this:

[COLOR=#333333]vpart2 = Cells(Icount, "B").Value   '*******ß---Need Help Here[/COLOR][COLOR=#333333] 
vcomplete = "SET datafile=" & Chr(34) & vpart1 & vpart2 & ".dat" & Chr(34) 
[/COLOR][COLOR=#333333]Sheets("Output").Cells(Icount + 25, "A").Value = vcomplete     '*******ß---Need Help Here

[/COLOR]

Honestly though, you are going through each cell anyway, with the for loop, so you can grab the data using that instead of calling out the cell each time.

    Range("B9").Select ' starting point
    Dim x As Variant
    Dim Icount As Integer
  
    
    For Each x In Range("B9", Range("B9").End(xlDown))   'For Loop searches column for a blank
    If x = "" Then Exit For                              'Ends the loop when it hits a blank
   
    vpart2 = x.Value   '*******ß---Need Help Here
    vcomplete = "SET datafile=" & Chr(34) & vpart1 & vpart2 & ".dat" & Chr(34)
    
    Sheets("Output").Cells(x.Row, "A").Value = vcomplete     '*******ß---Need Help Here
    
    Next

 

[Learner99]

Thank you that worked