Help needed to modify or change code

[John T]

Hi,

I currently have the following code which would put the inputted value into cell G40.
However my spreadsheet has now changed such that the destination cell wont always be row 40 in column G.
Can you ammend my code so that the figure in the MsgBox is inputted into the correct cell.

If it helps in the adajacent cell in column F is the word "Actual Cash". Maybe that cell could be found and then offest one to the right
Or the cell i need in column G is the second last cell in the column with data. Maybe a code that starts from the bottom and finds the first cell with data and moves one up.
Thanks in advance.

Sub MM1()
Dim ans As Variant
Do
ans = Application.InputBox("Enter PDQ Z Total", Type:=2)
 If ans = "" Or TypeName(ans) = "Boolean" Then
    MsgBox "You must enter a PDQ Value", vbCritical, "Invalid Value"
 ElseIf TypeName(ans) <> "Double" Then
    Range("G40").Value = ans
    Exit Sub
 End If
Loop
End Sub

 

[John T]

I think i've fixed it by adding the following but if there's a better way then please let me know.

Sub MM1()
Dim ans As Variant
Do
ans = Application.InputBox("Enter PDQ Z Total", Type:=2)
 If ans = "" Or TypeName(ans) = "Boolean" Then
    MsgBox "You must enter a PDQ Value", vbCritical, "Invalid Value"
 ElseIf TypeName(ans) <> "Double" Then
  Range("G1000").End(xlUp).Select
 ActiveCell.Offset(-1, 0).Select
 ActiveCell.Value = ans
    
    
    'Range("G40").Value = ans
    Exit Sub
 End If
Loop
End Sub

 

[Joe4]

You can simplify all these lines:

  Range("G1000").End(xlUp).Select
 ActiveCell.Offset(-1, 0).Select
 ActiveCell.Value = ans

down to just this:

  Range("G1000").End(xlUp).Offset(-1, 0).Value = ans

(there usually isn't any need to select the cells to work with them - most of that is the Macro Recorder's literal recording of each distinct step).

 

[John T]

Thanks for simplifying that.