0

I am trying to automate repetitive tasks by using .inputboxes in Excel.

Note: CopyAmt is an integer returned from another InputBox, userInputRange is type 8 box, and RowCnt is the Row.Count of userInputRange.

For i = 1 To CopyAmt
    userInputRange.Copy
    ActiveCell.Offset(RowCnt, 0).Activate
    ActiveCell.PasteSpecial
    ActiveSheet.Range(ActiveCell.Offset(, 24), ActiveCell.Offset(RowCnt - 1, 24)).Value = Application.InputBox(Prompt:="Internal Value",  Type:=2)
Next i

If a user was to click Cancel, I would want to exit the sub only after deleting the selection that was just pasted on line 4 of the above code. This is what I was trying.

For i = 1 To CopyAmt
    userInputRange.Copy
    ActiveCell.Offset(RowCnt, 0).Activate
    ActiveCell.PasteSpecial
    ActiveSheet.Range(ActiveCell.Offset(, 24), ActiveCell.Offset(RowCnt - 1, 24)).Value = Application.InputBox(Prompt:="Internal Value",  Type:=2)
    If ActiveSheet.Range(ActiveCell.Offset(, 24), ActiveCell.Offset(RowCnt - 1, 24)).Value = False Then
        Selection.Delete
        Exit Sub
    End If
Next i

This results in a mistype and I would love some clarification on the things I am doing wrong, or just not efficiently.

Thank you for your time.

GSerg
  • 76,472
  • 17
  • 159
  • 346
Sean Sullivan
  • 3
  • 4
  • There is certainly a lot of [inefficient things](https://stackoverflow.com/q/10714251/11683) here, but what is the error and on which line? – GSerg Feb 04 '23 at 12:57
  • Thanks for the reference link. Lots of good advice in there. The error occurs on line 6, the IF statement. When the if statement is present, I am unable to enter anything in the inputbox without getting the Type Error. However, the IF statement does work - if you click cancel the sub ends without error. It seems like the wording of the returned value in the If statement is changing the expected Type in the inputbox. – Sean Sullivan Feb 04 '23 at 19:14
  • On line 6 you refer to the `.Value` of a multicell range. `.Value` of a multicell range is a 2D array (as opposed to the `.Value` of a single cell range, which is a single value). You cannot compare a 2D array to a single value such as `False`. An attempt to do so results in a type mismatch error. – GSerg Feb 04 '23 at 20:18

1 Answers1

0

Sean,

Give this a try. Note: this is untested code but should work.

For i = 1 To CopyAmt

  userInputRange.Copy
  ActiveCell.Offset(RowCnt, 0).Activate '*** Changes ActiveCell!
  With ActiveCell                       '*** Use With block to simplify code
    .PasteSpecial
    Temp = Application.InputBox(Prompt:="Internal Value")
    If (Temp <> False) Then
      ActiveSheet.Range(.Offset(, 24), .Offset(RowCnt - 1, 24)).Value = Temp
    Else
      .ClearContents '*** You don't want to delete the cell just clear the value.
      Return         '*** Get me out of the Subroutine
    End If
    
  End With
  
Next i

Edited: Removed the Type=2 from the Application.InputBox line. You can test for the type of data your need in the latter code.

RetiredGeek
  • 2,980
  • 1
  • 7
  • 21