1

I am using Excel Visual Basic for Applications (VBA)

I have a Macro that is selecting a set of cells, cutting them and pasting them into a defined cell in another sheet that the user selects. However, I need to have the macro look for empty rows (represented as a 0) in the user-chosen spreadsheet, as data may already be in the spot where it is getting pasted.

My Code:

Sub Button11_Click()
'
' Button11_Click Macro
'

'
    Range("A2:K2").Select
    Selection.Cut
    Sheets(Application.InputBox("Type desired Team")).Select
    Range("B4").Select
    Sheets("ImporterSheet").Select
End Sub

Basically,

Range(B4).Select

needs to be replaced with 

Range(If ActiveCell <> 0 then ActiveCell.Offset(1,0). If ActiveCell = 0 then paste data here)
ehamwey
  • 63
  • 5

1 Answers1

1

Try this:

Range(Range("B:B").Find(0, [B1]).Address).Select

It selects the first cell with 0 in it in Column B.
Is this what you need?

BTW, try going through THIS which is definitely a good read to improve your coding.

Community
  • 1
  • 1
L42
  • 19,427
  • 11
  • 44
  • 68
  • Good but its placing it in Cell B1, I need it to start looking for 0's down at cell B4. – ehamwey Apr 10 '14 at 09:48
  • Then change `[B1]` to `[B4]`. – L42 Apr 10 '14 at 10:03
  • Thanks, but now I'm getting "Error 91: Object variable or With block variable not set" and its debugging on the line Range(Range("B:B").Find(0, [B1]).Address).Select – ehamwey Apr 10 '14 at 19:27