Almost there!

Okay so I scoured my new Excel VBA for dummies and cant find a solution to finish my new thingie I made. This is what I wrote so far


Range("N8:N200").Select
For Each Cell In Selection
If Cell.Value > "" Then


End If


I want my Then statement to put the formula N(x)-L(x) in the cell in Column P but only if the cell in N has a value. The ones that have blanks I dont want the formula to carry over to P. So I was on the right track I think. I just cant figure out how to end it. :( Can anyone help a newbie VBA user!

example

If N5 is greater than or equal to 0 then P5=N5-L5
If N6 is greater than or equal to 0 then P6=N6-L6

Try:

For Each Cell In Selection
If Cell.Value <> "" Then
Cell.Offset(0, 2).Value = "Your formula"
End If
Next


The "offset" syntax is (# of rows up or down, # of columns left or right) counting from the "current" cell.
A negative number counts upward or to the left; a positive one counts downward or to the right.

You can get the same effect with
Cells(Cell.Row, Cell.Column + 2).Value = "TEST"

strTemp = "=N" & Cell.Row & "-L" & Cell.Row
Cells(Cell.Row, Cell.Column + 2).Formula = strTemp

hrm I am not sure if you understand my problem. I dont want a message box. I want a formula to go into column P. I kinda rewrote what I was doing. maybe this will better explain


Sub LoopThruCells()
Dim cell As Range
For Each cell In Range("N8:N200")
If cell.Value > "" Then
cell.Offset(0, 2).Formula = "=(N*-L*)"

End If

Next cell


Its the Formula part im having trouble with. The * is the cell number but that changes with each loop. Its like a straight across the row formula. N8-L8, N9-L9 ect.

Nevermind. I figured it out. I did a record macro in excel and did that relative reference option thingie!

Sub LoopThruCells()
Dim cell As Range
For Each cell In Range("N8:N200")
If cell.Value > "" Then
cell.Offset(0, 2).Formula = "=RC[-2]-RC[-4]"

End If

Next cell

End Sub


woohoo!

Sorry; realized I'd answered the wrong thing and was editing my post while you were solving your problem!:o

The msgbox is just something I use as a catch-all to trap whether something has met an "if" condition or entered a loop.

The relative references also work very nicely; excellent solution!

thank you for your help! I saw your edit haha. Ill start playing with Msgboxes once I get a grasp on the basics because they can be very useful (and fun) :p
I encountered a problem when trying to run the 2nd loop

Sub LoopThruCells2()

Dim cell As Range
For Each cell In Range("P8:P200")
If cell.Value <= 0 Then
cell.Offset.Clear

End If

Next cell

End Sub

its giving me a mismatch error. im not sure if i even wrote it right. I want the cell to clear out the formula if it is giving me a #VALUE! answer to the formula. Like if it isnt giving me a number whether greater than or less than or equal to 0 then clear cell. I think im on the right track.

I must say you smartypants who help noobies like me are very helpful! To bad you didnt work in an office all together or I'd send cookies :p

You aren't giving it a complete address for the cell to clear.

Offset requires the full syntax:
Offset(row,col)

so:

cell.offset(0,2).clear

Why two loops?

For Each Cell In Range("N8:N200")
If Cell.Value > "" Then
Cell.Offset(0, 2).Formula = "=RC[-2]-RC[-4]"
Else
Cell.Offset(0, 2).Clear
End If

Never mind - I seem to be reading too fast today. :-p

I see now what you're doing.

The type mismatch is because you're comparing an error value to a number; but I'm not sure how to trap that - let me play with it a bit.

You can actually ditch the loop completely in the first case, if you put the conditional into the formula instead of into VB:
Sub LoopThruCells()
Range("N8:N200").Formula = "=IF(RC-2="""","""",RC-2-RC-4)"
End Sub

To check for error-type results, you can use the IsError() function:
Sub LoopThruCells2()

Dim cell As Range
For Each cell In Range("P8:P200")
If IsError(cell.Value) Then '...
Next cell

End Sub

But that's not an ideal way of doing things, better to construct the formula so that it doesn't do that in the first place, e.g. by using IF() as in the first example.

quick fix: Test the value before you enter the formula.

For Each Cell In Range("N8:N200")
If Cell.Value - Cell.Offset(0, -2).Value >= 0 Then
Cell.Offset(0, 2).Formula = "=RC[-2]-RC[-4]"
Else
Cell.Offset(0, 2).Clear
End If
Next

You could also use this test instead of "IF cell.value>0":
If WorksheetFunction.IsNumber(Cell.Value) Then
This will permit negative values, but shouldn't give you any #Value results.

Worksheet.Function is great. It lets you use keywords that the spreadsheet recognizes but VB doesn't (such as IsNumber - which I used here both to demonstrate the "worksheet.function.something" command and because I the VB equivalent to "IsNumber" eludes me at the moment). ;)

******edit******

Thanks, Cas!

aah you guys are awesome! With your guys help its working now! I cant thank you enough!

im going to have to play around with that worksheetfunction idea. I have a long ways to go to learn this stuff! :(