Solved

If, left function, loop, replace

Posted on 2012-03-26
4
225 Views
Last Modified: 2012-03-26
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
Comment
Question by:Seamus2626
4 Comments
 
LVL 41

Accepted Solution

by:
dlmille earned 250 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

Open in new window


Cheers,

Dave
0
 
LVL 46

Expert Comment

by:Martin Liss
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

by:Rory Archibald
Rory Archibald earned 250 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

Open in new window

0
 

Author Closing Comment

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

Thanks guys

Seamus
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

939 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

4 Experts available now in Live!

Get 1:1 Help Now