Excel macros (if statements etc) how?

[jayzeus]

Hello,

I'm working on a project where I have to write an Excel macro. I've never worked with excel that extensively, but I have a good computer/programming knowledge. I've worked with vb .net before, but looks like excel's macros have their own set of methods, and commands. So bear with me.

What I'm needing to do is to write/build a macro that will build a table and fill the columns according to some simple logic that I have here, while parsing two other column/worksheets for data.

It sounds pretty simple but I just don't know the syntax enough.

Here's an example of the logic:

Column W (all cells that is) in worksheet "worksheet A":
If column BT on worksheet "worksheet B" = 'some string' then Column W = "Y", else "N"

Of course this needs to run through as many times as there are entries in the corresponding columns, so I'm assuming that there needs to be some sort of a loop? Unless there's a method for that.

Any ideas on how I could accomplish this?

[Bob Phillips]

Off the top

[vba]
Dim LastRow As Long
Dim sh As Worksheet

Set sh = Worksheets("SheetA")

With Worksheets("SheetB")

LastRow = .Cells(.Rows.Count,"BT").End(xlUp).Row
For i = 1 To LastRow
If .Cells(i, "BT").Value "some string" Then

sh.Cells(i,"W").Value = "Y"
Else

sh.Cells(i,"W").Value = "N"
End If
Next i
End With
[/vba]

[greymalkin]

is it necessary that you use a macro? this could easily be accomplished by sheet logic. In cell W of worksheetA you could have this formula (assuming on row 1):

=IF(WorksheetB!BT1="some value","Y","N")

You have an if condition, then the value if true, then the value if false.

Once you do this on the first row you can simply drag the formula down as far as you need and it will dynamically adjust to the corresponding row on WorksheetB.

[jayzeus]

Off the top

[vba]
Dim LastRow As Long
Dim sh As Worksheet

Set sh = Worksheets("SheetA")

With Worksheets("SheetB")

LastRow = .Cells(.Rows.Count,"BT").End(xlUp).Row
For i = 1 To LastRow
If .Cells(i, "BT").Value "some string" Then

sh.Cells(i,"W").Value = "Y"
Else

sh.Cells(i,"W").Value = "N"
End If
Next i
End With
[/vba]

Thanks a lot! It seems to make sense, I'll try that out tomorrow! Thanks!

[jayzeus]

is it necessary that you use a macro? this could easily be accomplished by sheet logic. In cell W of worksheetA you could have this formula (assuming on row 1):

=IF(WorksheetB!BT1="some value","Y","N")

You have an if condition, then the value if true, then the value if false.

Once you do this on the first row you can simply drag the formula down as far as you need and it will dynamically adjust to the corresponding row on WorksheetB.

Well this is just a small piece of a bigger project, and it might contain many more of such logic. I think I can accomplish that much quicker, writing a macro since it'll get bigger.