VBA - looping help

[adam cook]

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/tutorial...9d71-a183e5a510ce/visual-studionet-2005-.aspx

 

[Otto Moehrbach]

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


in message news:[email protected]...

 

[adam cook]

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

Ada
We've all been there, done that, and learned, so do not feel stupid. Yo
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 I
statement. Also, you do not need to Select cells to work with them
Something like the following: Keep asking questions. By the way "<>" mean
"not equal to". HTH Ott
Sub TestEachLine(
Dim c As Rang
For Each c In Range("E2:E20"
If c <> "CONS" The
c.Offset(,-3) = UCase(c.Offset(,-2
c.Offset(,-4) = "ConsCode
End I
Next
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/tutorial...9-d63d6b178c13/get-unique-sid-for-machin.aspx

 

[Otto Moehrbach]

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

in message news:[email protected]...