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.

 

[Akuini]

Hi, @AutoMation42. Welcome to the Forum.

... 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.

What about col "ID", could it be moved to another column?

 

[AutoMation42]

Hi, @AutoMation42. Welcome to the Forum.

What about col "ID", could it be moved to another column?

It could be if it would make writing this coding easier but ideally it would stay where it is because in my actual spreadsheet (not the test one i provided) it is in a column that is several spaces away. Also I apologize for the late reply, work picked up quite drastically this last week so I didn't have time to check on my forum post.

 

[AutoMation42]

Hi, @AutoMation42. Welcome to the Forum.

What about col "ID", could it be moved to another column?

Hi again Akuini,

It took me some time but after an immense amount of research and utilizing information provided on this forum and several others, I managed to piece together a solution to the problem I had listed. It is likely not the cleanest and could be trimmed up by someone with more knowledge than myself but nonetheless, I will post my solution this weekend for anyone that has had a similar problem to my own.

 

[AutoMation42]

Private Sub CommandButton1_Click()
Dim ID As String
Dim rng As Range
ID = Application.InputBox("Enter a Valid ID Number", "ID Completion", Type:=1)
If Trim(ID) <> "" Then
With Sheets("Sheet1").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
rng.Offset(0, 6).Value = rng.Offset(0, 6).Value + rng.Offset(0, 8).Value
Else
MsgBox "The ID You Entered Could Not be Found"
End If
End With
End If
End Sub

The above code is what I used to complete this issue. It is of course changed to fit the data sample I provided along with the issue but with some minor changes it should fit any issue of the same nature I believe. Essentially, what this code does is locates the ID you enter into the inputbox and then targets the "Test Cost Remaining" that is in the same row as the ID and combines it with the "Test Paid to Date Column" that is also in the same row as the ID. I am sure that this code could be trimmed up to be a lot more efficient but as I am still brand new to coding, it is the best I could come up with utilizing this site and other resources meant to help solve coding issues. I hope this helps someone else as well. @Akuini I am only sending this at you so that you may read the solution I came up with and maybe help neaten it up for other desperate souls like myself, thank you all for the help.

 

[Akuini]

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.

Your code won't work correctly if col "Test Paid to Date" & "Test Cost Remaining" changed their location.
Try something like this:

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

'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 Sheets("Sheet1").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).Value = Cells(rng.Row, a).Value + Cells(rng.Row, b).Value
         Else
            MsgBox "The ID You Entered Could Not be Found"
        End If
    End With
End If

End Sub

 

[AutoMation42]

Your code won't work correctly if col "Test Paid to Date" & "Test Cost Remaining" changed their location.
Try something like this:

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

'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 Sheets("Sheet1").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).Value = Cells(rng.Row, a).Value + Cells(rng.Row, b).Value
         Else
            MsgBox "The ID You Entered Could Not be Found"
        End If
    End With
End If

End Sub

Hi @Akuini, Because I provided a test sample instead of the actual format that your provided code didn't work how it would have. I changed the code to the best of my knowledge to fit the actual format of the spreadsheet I am using but without success. I am attaching an image of this that has the actual format of the spreadsheet I am using and I have simply changed the titles to avoid accidental release of any confidential information. If you could help me tweak the code you wrote for me I would greatly appreciate it, thank you for all of your help so far!

 

[AutoMation42]

Hi @Akuini, Because I provided a test sample instead of the actual format that your provided code didn't work how it would have. I changed the code to the best of my knowledge to fit the actual format of the spreadsheet I am using but without success. I am attaching an image of this that has the actual format of the spreadsheet I am using and I have simply changed the titles to avoid accidental release of any confidential information. If you could help me tweak the code you wrote for me I would greatly appreciate it, thank you for all of your help so far!

Sorry I should have added this part before posting but, is there any way that the addition can be changed so that it doesn't replace the "Test Paid to Date" value with a new whole number? Basically, the formula bar of the cells in the "Test Paid to Date" column look like this "1+2+3+4+5" and when the "Test Cost Remaining is added to it, it becomes "12345" a new whole number and the math is correct but, I would instead like it to become "1+2+3+4+5+...n" to keep track of the individual pieces that make the values up if that is possible in VBA.

 

[AutoMation42]

Sorry I should have added this part before posting but, is there any way that the addition can be changed so that it doesn't replace the "Test Paid to Date" value with a new whole number? Basically, the formula bar of the cells in the "Test Paid to Date" column look like this "1+2+3+4+5" and when the "Test Cost Remaining is added to it, it becomes "12345" a new whole number and the math is correct but, I would instead like it to become "1+2+3+4+5+...n" to keep track of the individual pieces that make the values up if that is possible in VBA.

Hi again @Akuini I made a silly mistake in changing the coding to properly fit the live spreadsheet and your code now works beautifully. I even tried changing the column locations to ensure that it still worked and it was perfect. I would still like to change it so that the "Test Paid to Date" is not replaced with a whole number and instead the "Test Cost Remaining" is tacked on to the end of the addition chain in the "Test Paid to Date" formula bars respectively if that is possible. Thank you again, I appreciate your help greatly.

 

[Akuini]

I made a silly mistake in changing the coding to properly fit the live spreadsheet and your code now works beautifully.

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)"

I would still like to change it so that the "Test Paid to Date" is not replaced with a whole number and instead the "Test Cost Remaining" is tacked on to the end of the addition chain in the "Test Paid to Date" formula bars respectively if that is possible. Thank you again, I appreciate your help greatly.

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