Solved

If, left function, loop, replace

Posted on 2012-03-26
4
224 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 45

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

762 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

19 Experts available now in Live!

Get 1:1 Help Now