VBA to repeat output from InputBox until last row

Ngamia

New Member
Joined
Apr 26, 2021
Messages
2
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
Hi, I am usually able to figure out what to do just by searching the forum but I have been going round in circles for the past day and a half with no success.

My worksheet has a ProductCode column (B) that needs to be filled with a specific Internal Code that is input by the user. Once the user inputs the internal code, the script needs to append a suffix (.5555) to this input then replace the current content in the cell. It should do this for every row in column (B).

BEFORE
FolioNoProductCodePrice
1Breach24.50
2Osco12.65
3Ballen45.00

AFTER
FolioNoInternalCodePrice
1Troy.555524.50
2Troy.555512.65
3Troy.555545.00

I have gotten the following code to work on the active cell but I cannot get it to repeat all the way to the bottom.

VBA Code:
Private Sub InputProduct()
    Dim myValue As Variant
    myValue = InputBox("Enter Product Name")
    Range("B2").Value = myValue
    ActiveCell.Value = ActiveCell.Value & ".5555"
End Sub

I want to call it as part of a larger script that reformats the current report to a new format, therefore it needs to work when cell B2 is not the active cell.

Any help will be greatly appreciated.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,189
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
Private Sub InputProduct()
    Dim myValue As Variant
    myValue = InputBox("Enter Product Name")
    If myValue <> "" Then
      Range("B2", Range("B" & Rows.Count).End(xlUp)).Value = myValue & ".5555"
    End If
End Sub
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,189
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,136,864
Messages
5,678,212
Members
419,751
Latest member
richkings

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top