PDA

View Full Version : Solved: VBA Offset help



Emoncada
11-13-2012, 10:25 AM
I am trying to do the following.

I am copying data from one sheet to another and would like if in column B data starts with "DC" then Column C would be "Desktop" else "Laptop"

I tried the following

If Left(Range("B7:B400").Value, 2) = "DC" Then

Range("B7:B400").Offset(0, 1).Value = "Desktop"

Else

Range("B7:B400").Offset(0, 1).Value = "Laptop"

End If

But got an error.

Any help would be great.

GTO
11-13-2012, 10:53 AM
Try like:
Option Explicit

Sub Example()
Range("B1:B25").FormulaArray = "=IF(LEFT(A1:A25,2)=""DC"",""Desktop"",""Laptop"")"
Range("B1:B25").Value = Range("B1:B25").Value
End Sub

Emoncada
11-13-2012, 11:07 AM
GTO that works but problem with it is if cell is blank it's putting "Laptop".
Can we have it look at cell if value = "" then don't do anything?

GTO
11-13-2012, 11:24 AM
Change the formula that we are temporarily plunking in.

Range("B1:B25").FormulaArray = "=IF(A1:A25="""","""",IF(LEFT(A1:A25,2)=""DC"",""Desktop"",""Laptop""))"

Emoncada
11-13-2012, 11:55 AM
Perfect

Thanks