Macro that fills user defined range

Tigerexcel

Active Member
Joined
Mar 6, 2020
Messages
493
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I would like to write a macro that prompts the user for a range of cells and then prompts for text/numbers and places those text/numbers in each of the cells from the chosen range.
Parts of this code work but it doesn't complete the job.

Sub Fill_range1()
Dim myValue As Variant
Dim UserRange As Range
On Error GoTo Canceled
Set UserRange = Application.InputBox(Prompt:="Please Select Range", Title:="Range Select", Type:=8)
myValue = InputBox("Enter information to be repeated")
Range(UserRange).Value = myValue
'AutoFit All Columns on Worksheet
ThisWorkbook.Worksheets("Sheet1").Cells.EntireColumn.AutoFit
If MsgBox("Do you wish to delete this selection", vbYesNo) = vbYes Then Range("a1:t1000").ClearContents
End Sub
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Since UserRange is already a range variable, this line is incorrect:
VBA Code:
Range(UserRange).Value = myValue
It should simply be:
VBA Code:
UserRange.Value = myValue

Also, I see an issue with:
VBA Code:
On Error GoTo Canceled
as "Canceled" does not exist in your code.
 
Upvote 0
Thanks Joe, it works better but the autofit doesn't work now, any suggestions?

Sub Fill_range1()
Dim myValue As Variant
Dim UserRange As Range
'On Error GoTo canceled
Set UserRange = Application.InputBox(Prompt:="Please Select Range", Title:="Range Select", Type:=8)
myValue = InputBox("Enter information to be repeated")
UserRange.Value = myValue
'canceled
'AutoFit All Columns on Worksheet
ThisWorkbook.Worksheets("Sheet1").Cells.EntireColumn.AutoFit
If MsgBox("Do you wish to delete this selection", vbYesNo) = vbYes Then Range("a1:t1000").ClearContents
End Sub
 
Upvote 0
It seems to work just fine for me.
Are you sure that your sheet is really named "Sheet1"?
Maybe try changing that line to:
VBA Code:
ActiveSheet.Cells.EntireColumn.AutoFit
 
Upvote 0
Maybe try changing that line to:
VBA Code:
ActiveSheet.Cells.EntireColumn.AutoFit
Or if it just those newly filled values that you need to ensure fit in their columns, that line might just need to be
VBA Code:
UserRange.Columns.AutoFit
 
Upvote 0
It seems to work just fine for me.
Are you sure that your sheet is really named "Sheet1"?
Maybe try changing that line to:
VBA Code:
ActiveSheet.Cells.EntireColumn.AutoFit
Hi again Joe,

Definitely Sheet1, is there a way to tell whether the macro is referencing another worksheet? The VBA Project window at the top-left of the screen indicates that it's Sheet1. Is there anywhere else to check?
 
Upvote 0
Definitely Sheet1,
You need to be careful with how you reference the sheets. For example, sheets can be like below, in which case there can be confusion about whether Sheet1 is the yellow one or the green one. :)

If this was your workbook, then your code would be referencing the green one with this line
VBA Code:
ThisWorkbook.Worksheets("Sheet1").Cells.EntireColumn.AutoFit

1583970714007.png
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

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
Back
Top