How would i solve this problem in Visual Basic in Excel?

Question

In order to understand this question refer to the image I uploaded at "http://img252.imageshack.us/img252/2664/untitledii8.png"


ok my problem is this, i have over 7,500 transactions of accounting projects to do, and I have only 2 days to figure this out....I need to make a program using Excel Visual Basics to make this job go faster.(show me the code i need to type please!)...What I need it to do is:

Tell me if box H6 is equal to H7 (if yes, then does the sum of G6 plus G7 = zero? ((if yes then put a YES in I6 and I7)(if no, then put a NO in I6)) (in no then put NO in I6 and move down to box H7)

If H6 and H7 does not equal, then move down to box H7

After we have moved to H7, we need to see if H7 equals H8

If H7 equals H8 then (if yes then does the sum of G7 and G8 equal Zero? ((If yes then put a YES in I7 and I8) (if no then put a NO in I7 and move on to the next box down)) (If no then put a NO in I7 and move on to the next box)

and so on to transaction 7500....

http://img252.imageshack.us/img252/2664/untitledii8.png


This is the picture i took a snap shot of. refer to this photo!

Richard H

Tell me how to run a macro...I know some basic VB skills, but i dont know how to save a macro and use it to edit my work.

Also, would you tell me how to type the code? i'm kinda rusty in VB, and i need some help.

Answer 1

'Substitute your sheet name for Sheet1 if it is not Sheet1.

For Each x In [Sheet1! H:H]
If x.Text = “” Then
Exit For
End If

If x.Value = x.Offset(1, 0).Value Then
If x.Offset(0, -1).Value + x.Offset(1, -1).Value = 0 Then
x.Offset(0, 1).Value = “YES”
x.Offset(1, 1).Value = “YES”
Else
x.Offset(0, 1).Value = “NO”
End If
End If
Next x

This code will check ALL of the cells in column H on Sheet1 until it reaches the first blank cell in Column H and ends.

To enter and run the codel. Go to:

Tools > Macro > Record New Macro..

Write down the Macro name (default name for your first macro is Macro1), then hit OK.

Before you do anything in Excel, hit the stop button on the little toolbar that pops up. You don't actually want to record code right now, you just want to create a blank macro quickly to enter your code into.

Now go back and click:

Tools > Macro > Macros...

Then select the macro name you just created and click the Edit button. That will open up the macro code in Visual Basic Editor (VBE). Copy and paste the code I wrote then just switch out the name Sheet1 for the name of your worksheet or temporarily rename the sheet Sheet1 before you run the button. Make sure to save your workbook before you run the macro. You can't just click Undo to undo the changes made by a macro. Once you are ready to run the macro, you can either click the play button in VBE or you can go to:

Tools > Macro > Macros...

Then select your macro name and click the run button.

Yahoo Answers does seem to like tabbing so if you want better readability you'll have to figure out how it needs to be tabbed.

PS - This is not something that you necessarily need a macro for. You could achieve the same thing by using the IF function in a few of the cells and just copying the cell formula's down the column. The IF function can nested also.

If you put this formula in I6 and copied it down the column, it should work the same as all of that macro code.

= IF(H6 = H7, IF(G6 + G7 = 0, "YES", "NO"), "NO" )

If it were me, I'd go with the Excel function. It is much simpler.

Answer 2

Try recording a Macro to get what you want. You can then view the VBA code behind the macro.

The steps below are from Excel 2002 (Offie XP Pro), and might be slightly different, depending what version of Office you are running.

To record the macro, goto TOOLS>Macro>Record New Macro

Name the macro

A toolbar with VCR like buttons will pop up. Type the data and formulas into the spreadsheet

Stop the macro recorder when you're done

Go to Tools>Macro>Macros

A dialog box will pop up, select the macro from there, and click the "Edit" button This will show you the VBA code.