VBA - looping help
 

Hi again,
sorry ive posted twice today for VBA help. im very new to VBA so im seeking help where i can.
I have a spreadhseet with the following columns:
Code - A1
Name - Uppercase - B1
Name - Lowercase - C1
Title - D1
Status - E1

Now i need to create a simple loop that checks column E (status) for a status of "CONS". If the cell contains the word CONS then it will ignore that row and move onto the next row.If it doesnt contain the word "CONS" then it will copy cell C1 into B1 and uppercase THEN let me paste a code into cell A1.
Once this row is complete, it will need to go down to the next row, again pasting to cell A2 etc until there is no data left in column E

Can this be done easily?
I attempted this but failed miserably, my code is:

Sub TestEachLine()

Dim c As Range
For Each c In Range("E2:E20")

If c.Value = "CONS" Then
Next c
Else
Range("B2").Select
ActiveCell.FormulaR1C1 = "=UPPER(RC[1])"
Range("A2").Select
ActiveCell.FormulaR1C1 = "ConsCode"
End If

Next c
End Sub

Please dont be harsh on the code, im very new to this and still trying to learn. the "ConsCode" is just an example of what will be inserted, it will be a code calculated elsewhere so "ConsCode" is just a temp value for test purposes.
Thank you all again, and sorry to bug you twice in one day.
Adam


Submitted via EggHeadCafe - Software Developer Portal of Choice
VIsual Studio.NET 2005 / SQL 2005 Resources
http://www.eggheadcafe.com/tutorials...net-2005-.aspx

VBA - looping help
 

Adam
We've all been there, done that, and learned, so don't feel stupid. You
can have only one "Next c" per "For c...". Change the "If c.Value ="CONS"
to "If c<"CONS then you don't need to have an "Else" part of the IF
statement. Also, you do not need to Select cells to work with them.
Something like the following: Keep asking questions. By the way "<" means
"not equal to". HTH Otto
Sub TestEachLine()
Dim c As Range
For Each c In Range("E2:E20")
If c < "CONS" Then
c.Offset(,-3) = UCase(c.Offset(,-2)
c.Offset(,-4) = "ConsCode"
End If
Next c
End Sub


"adam cook" wrote in message ...
Hi again,
sorry ive posted twice today for VBA help. im very new to VBA so im
seeking help where i can.
I have a spreadhseet with the following columns:
Code - A1
Name - Uppercase - B1
Name - Lowercase - C1
Title - D1
Status - E1

Now i need to create a simple loop that checks column E (status) for a
status of "CONS". If the cell contains the word CONS then it will ignore
that row and move onto the next row.If it doesnt contain the word "CONS"
then it will copy cell C1 into B1 and uppercase THEN let me paste a code
into cell A1.
Once this row is complete, it will need to go down to the next row, again
pasting to cell A2 etc until there is no data left in column E

Can this be done easily?
I attempted this but failed miserably, my code is:

Sub TestEachLine()

Dim c As Range
For Each c In Range("E2:E20")

If c.Value = "CONS" Then
Next c
Else
Range("B2").Select
ActiveCell.FormulaR1C1 = "=UPPER(RC[1])"
Range("A2").Select
ActiveCell.FormulaR1C1 = "ConsCode"
End If

Next c
End Sub

Please dont be harsh on the code, im very new to this and still trying to
learn. the "ConsCode" is just an example of what will be inserted, it will
be a code calculated elsewhere so "ConsCode" is just a temp value for test
purposes.
Thank you all again, and sorry to bug you twice in one day.
Adam


Submitted via EggHeadCafe - Software Developer Portal of Choice
VIsual Studio.NET 2005 / SQL 2005 Resources
http://www.eggheadcafe.com/tutorials...net-2005-.aspx

Thank you! :)
 

Thank you Otto! that worked a treat. ive managed to create another macro to create the "ConsCode" and referenced it in the place of "ConsCode" as per your code.

Thank you so much again.

Adam



Otto Moehrbach wrote:

AdamWe've all been there, done that, and learned, so do not feel stupid.
11-Jan-10

Adam
We've all been there, done that, and learned, so do not feel stupid. You
can have only one "Next c" per "For c...". Change the "If c.Value ="CONS"
to "If c<"CONS then you do not need to have an "Else" part of the IF
statement. Also, you do not need to Select cells to work with them.
Something like the following: Keep asking questions. By the way "<" means
"not equal to". HTH Otto
Sub TestEachLine()
Dim c As Range
For Each c In Range("E2:E20")
If c < "CONS" Then
c.Offset(,-3) = UCase(c.Offset(,-2)
c.Offset(,-4) = "ConsCode"
End If
Next c
End Sub

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Get Unique SID for machine / user
http://www.eggheadcafe.com/tutorials...or-machin.aspx

Thank you! :)
 

Glad I was able to help. Thanks for the feedback. Otto

"adam cook" wrote in message ...
Thank you Otto! that worked a treat. ive managed to create another macro
to create the "ConsCode" and referenced it in the place of "ConsCode" as
per your code.

Thank you so much again.

Adam



Otto Moehrbach wrote:

AdamWe've all been there, done that, and learned, so do not feel stupid.
11-Jan-10

Adam
We've all been there, done that, and learned, so do not feel stupid. You
can have only one "Next c" per "For c...". Change the "If c.Value ="CONS"
to "If c<"CONS then you do not need to have an "Else" part of the IF
statement. Also, you do not need to Select cells to work with them.
Something like the following: Keep asking questions. By the way "<"
means
"not equal to". HTH Otto
Sub TestEachLine()
Dim c As Range
For Each c In Range("E2:E20")
If c < "CONS" Then
c.Offset(,-3) = UCase(c.Offset(,-2)
c.Offset(,-4) = "ConsCode"
End If
Next c
End Sub

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Get Unique SID for machine / user
http://www.eggheadcafe.com/tutorials...or-machin.aspx