PDA

View Full Version : Solved: Help required : syntax correction



satish gubbi
02-21-2012, 06:41 AM
Hi

I found the below code in VBA express forum which adds formula into the column, i have modified as per my requirement, however I am getting an error.

I need this code to place the formula if the cell value > 0 (if the cell has some value or it should be left blank)

Kindly help


Sub Fill()
Dim lLR As Long
lLR = Cells(Rows.Count, "B").End(xlUp).Row
With Range("D:D" & ILR)
If ILR > 0 Then
.Formula = "=IF(LEFT(RC[-2],3)=""FW:"",RIGHT(RC[-2],LEN(RC[-2])-4),IF(LEFT(RC[-2],3)=""RE:"",RIGHT(RC[-2],LEN(RC[-2])-4),RC[-2]))"
.Value = .Value
End If
End With
End Sub

Kenneth Hobs
02-21-2012, 07:25 AM
Option Explicit is your friend. Use it as the first line in a Module before your routines. When you do that and press the Compile button, you will notice that you dimmed LLR but referenced ILR.

I added a one to the start the Range from D1.

ILR would never be 0 so I added a loop to iterate each cell in the range.

Sub Fill()
Dim ILR As Long, c As Range
ILR = Cells(Rows.Count, "B").End(xlUp).Row
For Each c In Range("D1:D" & ILR)
With c
If .Value2 > 0 Then
.Formula = "=IF(LEFT(RC[-2],3)=""FW:"",RIGHT(RC[-2],LEN(RC[-2])-4),IF(LEFT(RC[-2],3)=""RE:"",RIGHT(RC[-2],LEN(RC[-2])-4),RC[-2]))"
.Value = .Value
End If
End With
Next c
End Sub

Bob Phillips
02-21-2012, 07:28 AM
Dim lLR As Long
lLR = Cells(Rows.Count, "B").End(xlUp).Row
With Range("D2:D" & lLR)

.Formula = "=IF(LEFT(RC[-2],3)=""FW:"",RIGHT(RC[-2],LEN(RC[-2])-4),IF(LEFT(RC[-2],3)=""RE:"",RIGHT(RC[-2],LEN(RC[-2])-4),RC[-2]))"
.Value = .Value
End With

satish gubbi
02-21-2012, 07:39 AM
Both Codes are working fine, Thank you very much for all your support