Excel Macro Help - IF statement related?

[pencilpusher]

Hi everyone,

I just discovered Macros recently, and have been messing with the macro recorder and meshing it with some internet research, but I have gotten stuck on how to fix the code I highlighted in bold.

The entire code filters a persons name by their account code and will ultimately pull up how much they are costing vs the available budget. If the budget is greater than or equal to 0, then it gets an "OK", the budget at the time of check is hard-coded in (in this case, copy and paste-values'd), then the date of the check is entered.

The problem I have is with the "OK", budget amount, and date section of the code.

If I just want to give an OK if the line passes budget, the code works perfectly. That is, pass budget will paste an "OK", a fail will leave it blank. But, if I also want to add the budget amount and date, weird things start happening. With the code below, a fail (no "OK") will still post the budgeted amount and date of budget check. I've tried in another version to rewrite this as an IF statement (IF cell = OK, then put budget amount and date) but if I do this, the "OK" text string will never show up, even if the line passes budget review.

Sub Macro2()
'
' Macro2 Macro
' Complex Macro
'
' Keyboard Shortcut: Ctrl+Shift+A
'

'notes which cell I clicked on first - at macro end, will return to this cell, then down 1 cell
    x = ActiveCell.Address

' Start at Name, move two cells over; copy contents and filter

    Selection.Offset(0, 2).Select
        Selection.Copy
            Range(Cells.Address).AutoFilter Field:=8, Criteria1:= _
            ActiveCell.Value, Operator:=xlAnd
            Application.CutCopyMode = False
            
' Move 1 cell over; copy contents and filter

    Selection.Offset(0, 1).Select
        Selection.Copy
            Range(Cells.Address).AutoFilter Field:=9, Criteria1:= _
            ActiveCell.Value, Operator:=xlAnd
            Application.CutCopyMode = False
            
'Move 1 cell over; copy contents and filter
    
    Selection.Offset(0, 1).Select
        Selection.Copy
            Range(Cells.Address).AutoFilter Field:=10, Criteria1:= _
            ActiveCell.Value, Operator:=xlAnd
            Application.CutCopyMode = False
    
'Move 1 cell over; copy contents and filter

    Selection.Offset(0, 1).Select
        Selection.Copy
            Range(Cells.Address).AutoFilter Field:=11, Criteria1:= _
            ActiveCell.Value, Operator:=xlAnd
            Application.CutCopyMode = False

'Move 1 cell over; copy contents and filter

    Selection.Offset(0, 1).Select
        Selection.Copy
            Range(Cells.Address).AutoFilter Field:=12, Criteria1:= _
            ActiveCell.Value, Operator:=xlAnd
            Application.CutCopyMode = False

'Move 1 cell over; copy contents and filter

    Selection.Offset(0, 1).Select
        Selection.Copy
            Range(Cells.Address).AutoFilter Field:=13, Criteria1:= _
            ActiveCell.Value, Operator:=xlAnd
            Application.CutCopyMode = False
            
'Move 1 cell over; copy contents and filter
    
    Selection.Offset(0, 1).Select
        Selection.Copy
            Range(Cells.Address).AutoFilter Field:=14, Criteria1:= _
            ActiveCell.Value, Operator:=xlAnd
            Application.CutCopyMode = False

[B]'Check Amount per Filtered Name vs Budget
    'Declare "availablebudget" for Long (what is Long?) and "result" as text variable
        Dim availablebudget As Long, result As String
    'This is cell R471 (471st row, 18th column) which is equal to (budget)-(Filtered Name amount)
        availablebudget = Cells(471, 18).Value
    'Declare "iRow" as an integer and returns the current row of the active cell
        iRow = ActiveCell.Row
    'If budget is greater than 0, paste text into cell
        If availablebudget >= 0 Then result = "OK"
        Cells(iRow, 17).Value = result
    
'add budgeted amount
    'Move 4 cells over right to Budget column, copy it
        Selection.Offset(0, 4).Select
        Selection.Copy
    'Move 1 cell over to right and paste special value
        Selection.Offset(0, 1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
        
'add date
    'Declare "iRowDate" as an integer and returns the current row of the active cell
        iRowDate = ActiveCell.Row
    'Put date in 20th column [cell = (row#, column#)]
        Cells(iRowDate, 20).Value = Date[/B]

'clear filters and exit
    ActiveSheet.ShowAllData

'Return to start position
    Range(x).Select
    
'move down 1 cell
    Selection.Offset(1, 0).Select

End Sub

Does anyone know how the highlighted code should be fixed? As I said at the beginning; if after the filtering, the line passes budget review, then the macro should paste an "OK". Then it should copy the budgeted amount and paste-value the amount into another cell, and finally put the date that this was done.

 

[WarPigl3t]

Hey. If no one helps you with this, then I can help you. I'm assuming no one will help you because you posted code without a dataset. Good job on the comments in the code. Every programmer codes differently. That said, I don't code using offsets. From what I can tell, you aren't asking for anything complicated, but I can't decipher your code due to that you didn't post a dataset. If I have to use my imagination to visualize your dataset, then my concentration to understand the code is reduced. See what I'm getting at? I can help you accomplish your task, but I'm going to need you to forget about your code. Pretend it doesn't exist. Now start by inserting a table into your thread. To do this, click on the "Go Advanced" button right next to the "Post Quick Reply" button. Now insert a table and click on the table options button. The icon looks like a table with a gear in it. Once you have the table options open, click on "Table Style" and "Full Grid". Then insert a dataset for me to follow. Here is what it should look like.

	A
1	data
2	data

<tbody>
</tbody>

Once you have entered a dataSet into a table, please provide an explanation of what you want to accomplish. Provide details about what where the output should go such as(the output should go in cell A1), and what needs to get done to reach that output such as(to reach the output, the code needs to multiply this times that). Then you can tell me if you want a function, subroutine, or on change event.
I'm pretty good with VB but I'm relatively new to VBA. This is why I don't use excel VBA specific commands such as offset in my coding. I use mostly standard VB code style such as Do While and For loops. I use counters rather than some of the other methods I've seen others use. If you can explain your dilemma to me in plain English, with complete sentences, and don't use big words that could lead to a misunderstanding, then I will be glad to help you write a working macro.

 

[pencilpusher]

Sure thing, thanks for your help.

The table below is an example of the spreadsheet after the filtering has been run. On this example the macro started on B2, moved over to D2 copied the contents and filters (repeat until J2). At this point, the sum of amounts under this account line will be generated (using the non-macro subtotal function) in L4, and the budget will be generated under N4 (non macro), with the difference being put into N6 (non-macro)

Since N6 is positive (>=0), M2 should say "OK", N2 will be copied, O2 will be paste-special values, and P2 will have the date stamped

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

Name

Account

Code 1

Code 2

Code 3

Code 4

Code 5

Code 6

Code 7

x

Amount

Status

Budget (as of Today)

Budgeted Amount

Check Date

2

Charlie Brown

1234567891011121314151617 <tbody> </tbody>

123

456

789

1011

1213

1415

1617

$1,000

$3,000

3

4

$1,000

$3,000

5

6

$2,000

<tbody>
</tbody>

Let me know if anything is unclear.

 

[WarPigl3t]

There are too many variables unanswered and I can see this thread going on for days. I'm not one to stay on a thread very long. I am off work for 2 days starting today. If no one helps you, I could schedule a meeting with you over the internet using software called TeamViewer. It will allow us to share screens and chat via voice like a phone call. If you agree then I ask a few things. Please have a dataset ready in your excel file with multiple rows. Please have a microphone. Please have speakers so that you can hear me. And please have at least one hour to dedicate to our conversation. I don't expect to finish the code during our conversation. I expect to start the code and have you answer some questions along the way about your dataset. Upon completion of the code, I will reply to this forum thread with a more detailed explanation of what you wanted and how I solved it. That way others that may have similar problems in the future can have the solution. If you are interested in scheduling a meeting, please provide a date, time, and time zone that you would like to meet. Make sure to check this thread right before the meeting time because I will post a link for you to click on that will route you to the meeting. I check excel every 1-3 hours so give ample notice of the time you want to meet.

 

[pencilpusher]

I don't have a microphone or webcam, so unfortunately what you are suggesting won't work for me.

I've linked to a file on dropbox which should show better how the file works.

https://www.dropbox.com/s/kn5icwhtzbnlldn/example.xlsx?dl=0

I really appreciate your offer for help. If there's anything else you can do for me, please let me know.

 

[WarPigl3t]

Sorry I'm giving up. I am not understanding the logic being used to split the cells of column B into the C:I columns. It appears to be random.

 

[pencilpusher]

You've actually got it backwards. Data is normally entered into columns C:I and combined into cell B. The vlookup that is mentioned in the excel sheet uses column B to lookup the budget for column M (there's a separate tab with all this info that I didn't include because the macro doesn't touch it).

If it makes the problem simpler, If the a cell is greater than or equal to 0, how do I put "ok" in one cell, copy another cell, paste the values into another cell, and then put the date in another cell. I know I have a basic framework for this.

As I mentioned in the OP, the only issue is with the code in bold. I cannot seem to get an "ok" to put a date and hardcoded budget amount at once.

 

[pencilpusher]

Let me expand a little on my question:

If a cell is greater than or equal to 0, (a) how do I put "ok" in one cell, (b) copy another cell, (c) paste the values into another cell, and then (d) put the date in another cell. The first cell with the budget uses a static cell that will never change value, but the cell for (a) (b), (c), and (d) will constantly change depending on which row you start at.

 

[WarPigl3t]

Okay so using your most recent table in this thread as an example, let's look at M2. If the account number located in C2 is not blank and it is greater than or equal to zero, then the value of M2 should say "ok". That can be done with a formula. You don't need a.macro.

=IF(and(b2<>"",b2>=0),"ok","")

Now let's look at the Bugeted Amount cell O2. I don't get why you want to copy the value of Buget (as of today) cell M2 into the cell O2. Why do you want 2 cells having the same data? Even though I don't get it, why don't you just have M2=O2 as the formula?

Now let's talk about the time stamp. This will need a macro. You will use the Today() method to accomplish this task. Here is what I understand so far. A user enters account numbers in a row of columns C through J. Those values are concatenated together into C2 which is the Account column. When this happens, P2 (Check Date) should enter today's date using the Today() method. You would have to make sure that the date doesn't change to today every time you run the macro. The date should only change when you enter the Account number for the first time. I'm writing this code from memory so I'm sorry if there are syntax errors that you'll have to troubleshoot.
Sub myDate()
lastRow = Range("A" & Rows.Count).Row
i = 2
Do While i <= lastRow
IF(Range("P" & i).value = "") Then
IF(Range("C" & i).value >= 0) Then
Range("P" & i).value = Today()
End IF
End IF
IF(Range("C" & i).value = "") Then
Range("P" & i).value = ""
End IF
i = i + 1
Loop
End Sub

 

[WarPigl3t]

In my last post. I said to use the Today method. That is not an excel method. It is an excel function. Change that line of code to ...
Range("P" & i).value = application.worksheetfunction.today()

Or you could try...
Range("P" & i).value = Format(Date.Now(), "m/dd/yyyy")