If column B not empty, value in column A = L
 

Hi there,
I'm trying to maybe modifying this code:
Dim i As Long
On Error Resume Next
For i = 1 To ActiveSheet.UsedRange.Rows.Count( Maybe change this to From
Range B5 to last cell where there is data)
If Cells(i, "B") = "" Then Change This Whats the syntax for Not empty?
Cells(i, "A").Value = "L"
End If
Next
So basically if there is data in Column B5, then put L in Column A.

Please advise
Thanks for any help
Juan

If column B not empty, value in column A = L
 

How about:

Sub gsnu()
dim r as range

Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1

For i = 5 To nLastRow
If IsEmpty(Cells(i, 2).Value) Then
Else
Cells(i, 1).Value = "L"
End If
Next
End Sub

--
Gary's Student


"Juan" wrote:

Hi there,
I'm trying to maybe modifying this code:
Dim i As Long
On Error Resume Next
For i = 1 To ActiveSheet.UsedRange.Rows.Count( Maybe change this to From
Range B5 to last cell where there is data)
If Cells(i, "B") = "" Then Change This Whats the syntax for Not empty?
Cells(i, "A").Value = "L"
End If
Next
So basically if there is data in Column B5, then put L in Column A.

Please advise
Thanks for any help
Juan

If column B not empty, value in column A = L
 

Sub AAA()
Dim rng As Range
Set rng = Range(Cells(1, 2), _
Cells(Rows.Count, 2).End(xlUp))
rng.Offset(0, -1).Formula = _
"=if(B1<"""",""L"",na())"
rng.Offset(0, -1).Formula = _
rng.Offset(0, -1).Value
On Error Resume Next
rng.Offset(0, -1).SpecialCells( _
xlConstants, xlErrors).ClearContents
On Error GoTo 0

End Sub

puts L where there is data in column B. I believe that is what you
described.

--
Regards,
Tom Ogilvy


"Juan" wrote:

Hi there,
I'm trying to maybe modifying this code:
Dim i As Long
On Error Resume Next
For i = 1 To ActiveSheet.UsedRange.Rows.Count( Maybe change this to From
Range B5 to last cell where there is data)
If Cells(i, "B") = "" Then Change This Whats the syntax for Not empty?
Cells(i, "A").Value = "L"
End If
Next
So basically if there is data in Column B5, then put L in Column A.

Please advise
Thanks for any help
Juan

If column B not empty, value in column A = L
 

Hello Gary and TOm,
thanks alot for the quick reply.
Gary, I tried yours and seems to work perfectly. Tom yours works but puts an
L in range I dont' want. Just want From B5. I have data in B4 and B3, so your
code will put in A3 and A4 and L, which I don't want this to do. But i should
be able to work around this.

So wanted to Thank both of you for the help.

Have a great weekend.

Juan

"Gary''s Student" wrote:

How about:

Sub gsnu()
dim r as range

Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1

For i = 5 To nLastRow
If IsEmpty(Cells(i, 2).Value) Then
Else
Cells(i, 1).Value = "L"
End If
Next
End Sub

--
Gary's Student


"Juan" wrote:

Hi there,
I'm trying to maybe modifying this code:
Dim i As Long
On Error Resume Next
For i = 1 To ActiveSheet.UsedRange.Rows.Count( Maybe change this to From
Range B5 to last cell where there is data)
If Cells(i, "B") = "" Then Change This Whats the syntax for Not empty?
Cells(i, "A").Value = "L"
End If
Next
So basically if there is data in Column B5, then put L in Column A.

Please advise
Thanks for any help
Juan

If column B not empty, value in column A = L
 

Guess I didn't read all the specifications closely enough
Sub AAA()
Dim rng As Range
Set rng = Range(Cells(5, 2), _
Cells(Rows.Count, 2).End(xlUp))
rng.Offset(0, -1).Formula = _
"=if(B1<"""",""L"",na())"
rng.Offset(0, -1).Formula = _
rng.Offset(0, -1).Value
On Error Resume Next
rng.Offset(0, -1).SpecialCells( _
xlConstants, xlErrors).ClearContents
On Error GoTo 0

End Sub

--
Regards,
Tom Ogilvy

"Juan" wrote:

Hello Gary and TOm,
thanks alot for the quick reply.
Gary, I tried yours and seems to work perfectly. Tom yours works but puts an
L in range I dont' want. Just want From B5. I have data in B4 and B3, so your
code will put in A3 and A4 and L, which I don't want this to do. But i should
be able to work around this.

So wanted to Thank both of you for the help.

Have a great weekend.

Juan

"Gary''s Student" wrote:

How about:

Sub gsnu()
dim r as range

Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1

For i = 5 To nLastRow
If IsEmpty(Cells(i, 2).Value) Then
Else
Cells(i, 1).Value = "L"
End If
Next
End Sub

--
Gary's Student


"Juan" wrote:

Hi there,
I'm trying to maybe modifying this code:
Dim i As Long
On Error Resume Next
For i = 1 To ActiveSheet.UsedRange.Rows.Count( Maybe change this to From
Range B5 to last cell where there is data)
If Cells(i, "B") = "" Then Change This Whats the syntax for Not empty?
Cells(i, "A").Value = "L"
End If
Next
So basically if there is data in Column B5, then put L in Column A.

Please advise
Thanks for any help
Juan