Changing Information Using InputBox Data as the Condition

[AutoMation42]

Hi Everyone,

I am new to mrexcel and VBA coding. I have never taken a coding class and am completely new to VBA. I have been self teaching as I go before signing up for a couple VBA and SQL classes but I still do not understand a lot of the lingo so if you can help, please break it down as if you are talking to a baby. I hope that my current problem can be solved so I will do my best to explain it. I have a test spreadsheet with information on it. Of this information only 3 columns will matter, the ID, Test Paid to Date, and Test Cost Remaining. Now I will explain the formula references. The ID is simply an ID, the Paid to Date is a number value, and the Cost Remaining is a formula, the Paid to Date - Test Budget. This means what I desire to do would cause a circular reference if I pull formulas using VBA. Now I will explain what I want my coding to do (which it is currently failing to do likely because of poor understanding on my part). Ideally, an InputBox will pop up, I will type in the ID#, then the Test Cost Remaining value (not the formula because that would cause a circular reference) will be added to the Test Paid to Date. This will cause the Test Paid to Date to increase by the Test Cost Remaining and the Test Cost Remaining to be reduced by its own value resulting in Test Cost remaining being zeroed out (but the test cost remaining formula should not be changed). Additionally, new rows and columns are constantly being added and removed but the ones I am working with will always have the same column title even if they are not in the same location. Also, if an ID number that is typed in cannot be found, I want a message box to appear that says "The ID Entered Cannot be Found on this Spreadsheet." and then have the input box reappear to type in a different number. I haven't even attempted to add the message box part as I have no idea how. Attached is an image of the test spreadsheet and the incomplete rudimentary code that I have written so far. If anyone can help me accomplish this I would greatly appreciate it.

 

[AutoMation42]

The header should be at row 1, otherwise you need to change it in this part:

'find col "Test Paid to Date" & "Test Cost Remaining" in row 1
a = Application.Match("Test Paid to Date", Rows(1), 0)
b = Application.Match("Test Cost Remaining", Rows(1), 0)

so, if the header is in row 2 then change "Rows(1)" to "Rows(2)"


Try changing this part:

                Cells(rng.Row, a).Value = Cells(rng.Row, a).Value + Cells(rng.Row, b).Value

to this:

                Cells(rng.Row, a).Formula = Cells(rng.Row, a).Formula & "+" & Cells(rng.Row, b).Value

@Akuini Thank you very much for all of your help, everything in this code now works exactly as I wanted it to and it makes my life significantly easier, not having to ctrl find everything and change it myself. I have other code that I have written for this spreadsheet as well. Would you mind helping me clean it up? It already works but is not dynamic and could use a few tweaks from a professional to be more efficient. If you are willing I could just post them in here with description and imaging or leave links to what will be my other posts, and thank you again for all of your help.

 

[AutoMation42]

@Akuini Thank you very much for all of your help, everything in this code now works exactly as I wanted it to and it makes my life significantly easier, not having to ctrl find everything and change it myself. I have other code that I have written for this spreadsheet as well. Would you mind helping me clean it up? It already works but is not dynamic and could use a few tweaks from a professional to be more efficient. If you are willing I could just post them in here with description and imaging or leave links to what will be my other posts, and thank you again for all of your help.

@Akuini Hi sorry I keep "@"ing you but you have been extremely helpful. I want to make a hopefully minor change, I changed the name of the sheet I had these buttons on because I want to make it into a button dashboard of sorts as I will have various buttons that are going to apply to different sheets. However, because these buttons are no longer applying to the sheet they are on, they aren't working. I thought it would be as simple as placing a line at the top of the code that read "ThisWorkbook.Worksheets("name_of_page_this_code_should_effect").Activate" but I was clearly mistaken. How can I make these buttons effect the correct sheet again? There is a new sheet I made that is the same name as what the command buttons were previously on and for the sake of this lets just say the Button Page is now called "Dashboard Control" and the sheet I want these buttons to effect is called "Active Stuff". Do you think you could help me fix this? Thank you again, your skills have made my life massively easier thus far.

 

[Akuini]

Try it like this:
Put this code below on a code module, say Module1:

Sub AutoMation42()
Dim ID As String
Dim rng As Range
Dim fm, a, b

Sheets("Active Stuff").Activate
'find col "Test Paid to Date" & "Test Cost Remaining" in row 1
a = Application.Match("Test Paid to Date", Rows(1), 0)
b = Application.Match("Test Cost Remaining", Rows(1), 0)
    
ID = Application.InputBox("Enter a Valid ID Number", "ID Completion", Type:=1)
If Trim(ID) <> "" Then
    With Range("A:A")
        Set rng = .Find(What:=ID, _
        After:=.Cells(.Cells.Count), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False)
        
        If Not rng Is Nothing Then
            Cells(rng.Row, a).Formula = Cells(rng.Row, a).Formula & "+" & Cells(rng.Row, b).Value
         Else
            MsgBox "The ID You Entered Could Not be Found"
        End If
    End With
End If

End Sub

Then in sheet "Dashboard Control" code module, you can call it from your command button like this:

Private Sub CommandButton1_Click()
    Call Module1.AutoMation42
End Sub