-1

I would like to write a code in VBA which copies formula from range("A3:H3") to these columns until there is data in column J. I've tried this code:

Sub fill_up()
If Not IsNull(Range("J3:J30000")) Then
Range("A3:H3").Select
Selection.Copy
Range("A4:H30000").PasteSpecial xlPasteFormulas
End If
End Sub

But this way I get filled up the columns until the 30000. row anyways. Another code I wrote for a command button:

Private Sub CommandButton1_Click()
Dim i As Integer
Dim j As Integer
i = 3
j = 4
If Not IsEmpty(Cells(i, 9)) Then
Range("A3:H3").Select
Selection.Copy
Cells(j, 1).PasteSpecial xlPasteFormulas
Do until j > 30000
i = i + 1
j = j + 1
Loop
End If
End Sub

But here the formula is only pasted to the 4. row.

Any suggestions?

RenataKiss
  • 1
  • 1

2 Answers2

0

Something like this:

sub test1()

iRow = 5
Do Until not IsEmpty(cells(iRow,10))
    Range(cells(3,1), cells(3,8)).Copy

    Range(cells(iRow,1), cells(iRow,8)).PasteSpecial Paste:=xlPasteFormulas

    iRow = iRow + 1
Loop

End Sub

That won't copy anything into the row that contains data in col J. If you want to include that row for pasting, then you could add a further single paste op after the do loop as a quick 'n' dirty fix.

Amiga500
  • 1,258
  • 1
  • 6
  • 11
-1

I recommend referencing the last cell containing data using ActiveSheet.Usedrange.Rows.Count property.

In your case:

Range("A4:H" & ActiveSheet.UsedRange.Rows.Count)
mmikesy90
  • 783
  • 1
  • 4
  • 11
  • Using that approach; if you've several entries in col J your only going to grab the last one, not the first one. – Amiga500 Mar 26 '21 at 14:16
  • [UsedRange is unreliable](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). – BigBen Mar 26 '21 at 14:30
  • I'm not following you. If say the last is J300, this expression will select range A4:H300, which matches the original goal "until there's data in column J". The pitfall of using Usedrang could be the case where there's another column having data after row 300. – mmikesy90 Mar 26 '21 at 14:35
  • @BigBen ha-ha, OK, that article showcases some very dumb use of Excel... It's not about UsedRange being unreliable, more like people don't understand stuff. – mmikesy90 Mar 26 '21 at 14:38
  • No, a *main* part of that question is that `UsedRange` is unreliable and the *wrong* way to find the last cell. `Dim lastRow As Long`, `lastRow = Cells(Rows.Count, "J").End(xlUp).Row` is more appropriate. In other words, what is confusing about "UsedRange should **NEVER** be used to find the last cell which has data. It is highly unreliable". Please read more carefully. – BigBen Mar 26 '21 at 14:43