If then statement 2

[WaterSprite]

ThisRow = Cells(65536,33).End(xlup).Row
Cells(ThisRow, 33).Activate
For i = ThisRow to 9 Step -1
if Cells(i, "AG") > 1 Then Cells(i, "AJ").value = "=(Cells(i, AG)-Cells(i,AH)))"
Next i
End sub

This works down to the if statement. I looked in the FAQ's and also searched under If Then, but nothing came up that gave me a clue as to what I did wrong.
The second line of code that activates the cell is probably not needed in this instant, but I use CurrentRegion a lot and choosing the active cell helps me with that.

Thanks for the help.

 

[SkipVought]

Hi,

To assign a FORMULA...

ThisRow = Cells(65536,33).End(xlup).Row

For i = ThisRow to 9 Step -1
if Cells(i, "AG") > 1 Then Cells(i, "AJ").formula = "=" & Cells(i, "AG").address & "-" & Cells(i,"AH").address
Next i
End sub

To assign a VALUE...

ThisRow = Cells(65536,33).End(xlup).Row

For i = ThisRow to 9 Step -1
if Cells(i, "AG") > 1 Then Cells(i, "AJ").value = Cells(i, "AG") - Cells(i,"AH")
Next i
End sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[WaterSprite]

Skip, thanks for your help. As usual, I made it to complicated.
However, my question and your reply made me realize I am very weak when it comes to variables. I sort of understand what I am writing when I write For i = ThisRow to 9 Step -1.
I can do stuff with Rows fairly well now.

So this is my actual question. How do I write the equivalant variable code for Columns? Or do I need to. Is it ok to always refer to columns by number or letter. I looked in the FAQ's and did a search for both Columns, and then Variables, but nothing I looked at hit me over the head and made the light come on.

 

[SkipVought]

a similar example...

ThisCol = Cells(1,255).End(xltoleft).column

For i = ThisCol to 9 Step -1
if Cells(2, i) > 1 Then Cells(2,i).value = Cells(3,i) - Cells(4,i)
Next i

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

[Edit]
I was called away from my desk for the last hour or so and I now see that Skip already covered your question. But I'll still post the following since there is some additional information that might be helpful to the OP.
[/Edit]

Notice that when you type "Cells(", a quick tip pops up and tells you that the arguments are ([red][RowIndex][/red], [blue][ColumnIndex][/blue]).

So column is just the part after the comma.

If you wanted to loop through each column in the first row, you could use

...
LstCol = Cells([red]1[/red],[blue]256[/blue]).End(xltoleft).Column

For i = LstCol to 1 Step -1
if Cells(1, i)...
Next i
...

Notice that I used 256 for the column and skip used 65536 for the row. That's because those are the limits for how many rows and columns are available up until Excel 2007. If you want to make your code more likely to transfer well to Excel 2007, you can instead calculate how many rows/columns the spreadsheet contains.

[tab]LstRow = Cells([red]ActiveSheet.Rows.Count[/red], [blue]1[/blue]).End(xlUp).Row
[tab]LstCol = Cells([red]1[/red], [blue]ActiveSheet.Columns.Count[/blue]).End(xlToLeft).Column

I sort of understand what I am writing when I write For i = ThisRow to 9 Step -1

Let's see if we can get rid of that "sort of".

you can use any variable you want, "i" is just really common in this case. But you could use "Ted" if you wanted to.

So you are assigning i (or ted) to represent a number equal to the last populated row in column AG (as specified by "i = ThisRow").

As you know, you are looping through that section of code. On each pass, the number that i represents decrements by one (as specified by "-1").

So if it starts off as 500, then the second pass it is equal to 499, then 498 and so on. This continues until i is equal to 9 (as specified by "to 9").

If you haven't already done this, step through the code by repeatedly pressing [F8]. You can hover over variables to see what, if any, value they represent, and see when and how they change.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[WaterSprite]

Skip, AnotherHiggins,

Thank you for the lesson. I now understand that I can use variables for both the colums and the rows. So, I think this afternoon I will practice my variables and maybe my code will run better.

Thanks again to the both of you. I appreciate the help.