Help with vba code

daveyscouse

Board Regular
Joined
Sep 14, 2004
Messages
77
Hi Guys

I am using the following code.

The first section runs if G11 is "". However if it is populated it goes to debug.

it debugs at the line I have marked ###

Can anyone help with this one please.

Davey

Private Sub CommandButton2_Click()

If Range("G11") = "" Then


Dim FromRange As Range
Dim ToRow As Long
'--------------------------------------------
Set FromRange = ActiveSheet.Range("F11")
'---------------------------------------------

ToRow = 16
While ActiveSheet.Cells(ToRow, 4).Value <> ""
ToRow = ToRow + 1
Wend
'---------------------------------------------
FromRange.Copy _
Destination:=ActiveSheet.Cells(ToRow, 4)

Dim FromRange2 As Range
Dim ToRow2 As Long
'--------------------------------------------
Set FromRange2 = ActiveSheet.Range("H11:L11")
'---------------------------------------------

ToRow2 = 16
While ActiveSheet.Cells(ToRow, 8).Value <> ""
ToRow2 = ToRow + 1
Wend
'---------------------------------------------
FromRange2.Copy _
Destination:=ActiveSheet.Cells(ToRow, 8)

Else


Dim FromRange3 As Range
Dim ToRow3 As Long
'--------------------------------------------
Set FromRange3 = ActiveSheet.Range("F11:G11")
'---------------------------------------------

ToRow3 = 16

###While ActiveSheet.Cells(ToRow, 4).Value <> ""###

ToRow3 = ToRow + 1
Wend
'---------------------------------------------
FromRange3.Copy _
Destination:=ActiveSheet.Cells(ToRow, 4)

Dim FromRange4 As Range
Dim ToRow4 As Long
'--------------------------------------------
Set FromRange4 = ActiveSheet.Range("H11:L11")
'---------------------------------------------

ToRow4 = 16
While ActiveSheet.Cells(ToRow, 8).Value <> ""
ToRow4 = ToRow + 1
Wend
'---------------------------------------------
FromRange4.Copy _
Destination:=ActiveSheet.Cells(ToRow, 8)


End If


Range("F11") = 0
Range("G11").Value = ""
Range("H11:L11") = 0
End Sub
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

daveyscouse

Board Regular
Joined
Sep 14, 2004
Messages
77
Hiya J

erm I'm not sure, I am using a piece of converted code I used some time ago.

To point out. if I take out the if statement and only use the later part ie range3 and range 4 it works fine

Have also tried the other way as in

if G11 > "" then

but still after the "Else" statment if debugs. ?? :oops:
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Well, add this line

MsgBox ToRow

immediately above the line that errors out and see if a zero pops out.
 

daveyscouse

Board Regular
Joined
Sep 14, 2004
Messages
77
Hiya J_Jon, been away all weekend

I have put in the message box, and yes a 0 does pop out, when I click ok it debugs again.

Any more help from you or anyone would be more thean welcome.

Davey
 

Watch MrExcel Video

Forum statistics

Threads
1,118,799
Messages
5,574,381
Members
412,589
Latest member
ArtBOM
Top