There's a couple of things that you may need to reconsider.
First of all, your code looks SPECIFICALLY at cell B1 and modifies A1.
you need a method of "cycling through" your values.
there are several ways to accomplish this.
In this case, I'd probably elect to do something like:
For each Rng in Range("B:B")
...
Next Rng
Now, the bad part of this is that it will process ALL rows.
If you only have a couple thousand, you'd be wasting LOTS of time checking the
remaining 1 million +.
So, you'd want to stop it at some point.
As long as ALL of the rows you want to process have a value in column B,
you could use:
For each Rng in Range("B:B")
If Rng.Value = "" then Exit For
...
Next Rng
In your code you're using:
Range("A1").Value = result
ONLY when the value in B is "KC" and doing nothing at other times.
Is that what you intended? or do you need to do this AFTER the If/elseif/Endif
statement?
regardless, you don't want to update A1 every time, but the A column of the row
being processed.
you can do this a couple of ways.
the .Offset method:
Rng.Offset(0, -1).Value = result
would indicate that the cell 1 cell to the left of the current row (0) would be
changed.
you could also use:
Cells(rng.row,"A").Value = Result
so you end up with:
=========================================================
Dim branch As String, result As String
Dim Rng As Range
For Each Rng In Range("B:B")
If (Rng.Value = "") Then Exit For
branch = Rng.Value
If branch = "LW" Then
result = "LW"
ElseIf branch = "LN" Then
result = "LN"
Else
result = "KC"
End If
Rng.Offset(0, -1).Value = result
Next Rng
=========================================================
I'm sure that you've got something else going on and this is simply a "snippet"
of code.
Otherwise, I'm not sure what the purpose of the if/elseif/endif statement is.
You could always use:
For Each Rng In Range("B:B")
If (Rng.Value = "") Then Exit For
Cells(Rng.Row, "A").Value = Rng.Value
Next Rng
Paul
-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
From: JenIT <jen...@alliedstaffing.com>
>To: excel-macros@googlegroups.com
>Sent: Monday, November 10, 2014 9:00 AM
>Subject: $$Excel-Macros$$ If then elseif on same sheet not looping
>
>
>
>Hello,
>I'm sure the answer to this is out there, but I am not searching correctly to
>find it. I have code to past the result of an if/then/elseif statement from
>column B into column A. It works for line one and I am drawing a mind blank
>and I can't get it to run through the whole sheet (variable length). I've
>tried a lot of different things, but then it stops working all together.
>Below is what I have. I am not at all a VBA expert, but am usually good at
>recording an modifying, but it has been a while and this time I am stumped.
>
>
>
>
> 'set location
>
>Range("B:B").Select
>
>Dim branch As String, result As String
>
>branch = Range("B1").Value
>
>If branch = "LW" Then
>
> result = "LW"
>ElseIf branch = "LN" Then
>
> result = "LN"
>Else
> result = "KC"
>Range("A1").Value = result
>
>
> End If
>
>
>End Sub
>
>
--
>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>https://www.facebook.com/discussexcel
>
>FORUM RULES
>
>1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
>quick attention or may not be answered.
>2) Don't post a question in the thread of another member.
>3) Don't post questions regarding breaking or bypassing any security measure.
>4) Acknowledge the responses you receive, good or bad.
>5) Jobs posting is not allowed.
>6) Sharing copyrighted material and their links is not allowed.
>
>NOTE : Don't ever post confidential data in a workbook. Forum owners and
>members are not responsible for any loss.
>---
>You received this message because you are subscribed to the Google Groups "MS
>EXCEL AND VBA MACROS" group.
>To unsubscribe from this group and stop receiving emails from it, send an
>email to excel-macros+unsubscr...@googlegroups.com.
>To post to this group, send email to excel-macros@googlegroups.com.
>Visit this group at http://groups.google.com/group/excel-macros.
>For more options, visit https://groups.google.com/d/optout.
>
>
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
