Problem with Macro setting range

[nicolewh]

I have this macro:

Sub ExampleTesting()
Dim LR As Long, i As Long, j As Long, ct As Integer
LR = Sheets("sheet2").Range("C" & Rows.Count).End(xlUp).Row
For i = 1 To LR Step 1
    j = j + 1
    ct = UBound(Split(Sheets("sheet2").Range("C" & j).Text, " ")) + 1
    If ct < 2 Then
    Sheets("sheet3").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = _
Sheets("sheet2").Range("A" & j: "D" & j).Value
    End If
Next i
End Sub

What am I doing wrong? Any suggestions?

Thanks

Nicole

 

[repairman615]

I have this macro:

Sub ExampleTesting()
Dim LR As Long, i As Long, j As Long, ct As Integer
LR = Sheets("sheet2").Range("C" & Rows.Count).End(xlUp).Row
For i = 1 To LR Step 1
    j = j + 1
    ct = UBound(Split(Sheets("sheet2").Range("C" & j).Text, " ")) + 1
    If ct < 2 Then
    Sheets("sheet3").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = _
Sheets("sheet2").Range("A" & j: "D" & j).Value
    End If
Next i
End Sub

What am I doing wrong? Any suggestions?

Thanks

Nicole

Maybe..:

Sheets("sheet2").[COLOR=red]Range("A" & j & ":" & "D" & j)[/COLOR].Value

 

[nicolewh]

On the same lines this won't work:

Dim j As Long
j = 3
Sheets("sheet2").Range("A" & j & ":" & "D" & j).Select

Why? and how do I fix it. Thanks

 

[repairman615]

I did get this to select range "a3:d3"

Does that help?

Sub Macro1()
'
' Macro1 Macro
'
'
    Dim j As Long
    j = 3
    Range("A" & j & ":" & "d" & j).Select
   ' Range("A3:D3").Select
End Sub

 

[nicolewh]

Your right. strange . thanks

 

[VoG]

If Sheet2 is not the active sheet you'll need

Application.Goto Sheets("sheet2").Range("A" & j & ":" & "D" & j)

 

[repairman615]

Thanks Vog, I was just in the help file trying to figure out the sheets(2) problem.

I greatly appreciate these great questions along with the great answers.