Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# If, left function, loop, replace

Posted on 2012-03-26
Medium Priority
233 Views
Hi,

I need a piece of code to count rows "A" then do do the following

IF Left(D2,2) = "BR" then W2 = "Paris" else W2 = "New York"

And loop till the last row.

Can anyone put that in structure for me!

Thanks
Seamus
0
Question by:Seamus2626

LVL 42

Accepted Solution

dlmille earned 1000 total points
ID: 37766616
When you say "count rows A" do you mean for each row having data in Column A do this task?  That's what this code does:

``````Sub doTheWork()
Dim wks As Worksheet
Dim rng As Range
Dim r As Range

Set wks = ActiveSheet

Set rng = wks.Range("A1", wks.Range("A" & wks.Rows.Count).End(xlUp))

For Each r In rng
If Left(wks.Range("D" & r.Row).Value, 2) = "BR" Then
wks.Range("W" & r.Row).Value = "Paris"
Else
wks.Range("W" & r.Row).Value = "New York"
End If
Next r

End Sub
``````

Cheers,

Dave
0

LVL 49

Expert Comment

ID: 37766620
What you are asking is a little confusing because it seems like you want to loop through the rows but your example uses cell D2. Is this waht you want

Dim r As Range
Dim i As Long

Set r = Range("A1").End(xlDown).Offset(0, 0)
For i = 1 To r.Row

If Range("D" & 1).Value = "BR" Then
Range("W" & i).Value = "Paris"
Else
Range("W" & 1).Value = "New York"
End If

Next
0

LVL 85

Assisted Solution

Rory Archibald earned 1000 total points
ID: 37766648
Or:
``````Sub testing()
Dim lngLastrow As Long
lngLastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("W2:W" & lngLastrow) = Evaluate("IF(LEFT(A2:A" & lngLastrow & ",2)=""BR"",""Paris"", ""New York"")")
End Sub
``````
0

Author Closing Comment

ID: 37766856
Hey Martin, that solution didnt work for me.

Thanks guys

Seamus
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
###### Suggested Courses
Course of the Month8 days, left to enroll