Looping Problem

[ryguy7272]

I am trying to resolve a problem for one of the directors at work. I am
attempting to place the value in ‘rRange’ in each Z1 cell in each sheet in
the workbook. I think I am pretty close, but I just can’t get the For-Next
looping part right. All attempts have been futile up to this point. Any
help would be greatly appreciated.

Sub StartHere()

Dim rRange As Range


On Error Resume Next
Application.DisplayAlerts = False
Set rRange = Application.InputBox(Prompt:= _
"Please select a range with your Mouse to be bolded.", _
Title:="SPECIFY RANGE", Type:=8)
Application.DisplayAlerts = True

Dim ws As Worksheet
For Each ws In Sheets
If ws.Visible Then ws.Select (False)
Range("Z1").Value = rRange
Next

On Error GoTo InValidEntry


InValidEntry:
If Err = 13 Then
MsgBox "Not a valid input. " & "Please retry."
End If


End Sub

 

[Dave Peterson]

I'd use something like this:

Option Explicit
Sub StartHere()

Dim rRange As Range
Dim ws As Worksheet

Set rRange = Nothing
On Error Resume Next
Set rRange = Application.InputBox(Prompt:= _
"Please select a range with your Mouse to be bolded.", _
Title:="SPECIFY RANGE", Type:=8)
On Error GoTo 0

If rRange Is Nothing Then
Exit Sub
End If

For Each ws In Sheets
If ws.Visible = xlSheetVisible Then
ws.Range("Z1").Value = rRange.Cells(1).Value
End If
Next ws

End Sub

But I don't see how this bolds the cell.

 

[ryguy7272]

This is sooooo close, but what I wanted was the value of the selected cell
(rRange) from each worksheet to be in each Z1 in each worksheet. The way it
is now, the first value selected shows in each worksheet, this value needs to
be looped...somehow. I had this working a different way; the user couldn't
select the input with Application.InputBox. I used Index/Match in the former
version, but the director said he wanted the flexibility to choose the value
that is input into those Z1 cells. Any additional thoughts? I'm going to
keep at it. Eventually I'll resolve this through brute force, but I'd really
appreciate it if someone can give me a hand here.

Thanks!!!

 

[Dave Peterson]

So if I picked A1 as the range, then I'd want each sheet's A1 to be in Z1 of
that same sheet??

If yes...

ws.Range("Z1").Value = ws.range(rRange.cells(1).address).Value

This is sooooo close, but what I wanted was the value of the selected cell
(rRange) from each worksheet to be in each Z1 in each worksheet. The way it
is now, the first value selected shows in each worksheet, this value needs to
be looped...somehow. I had this working a different way; the user couldn't
select the input with Application.InputBox. I used Index/Match in the former
version, but the director said he wanted the flexibility to choose the value
that is input into those Z1 cells. Any additional thoughts? I'm going to
keep at it. Eventually I'll resolve this through brute force, but I'd really
appreciate it if someone can give me a hand here.

Thanks!!!

 

[ryguy7272]

Yep, that's it!! Thank you so much!!
Here is the final version...

Sub StartHere()

Dim rRange As Range
Dim ws As Worksheet

Set rRange = Nothing
On Error Resume Next
Set rRange = Application.InputBox(Prompt:= _
"Please select a range with your Mouse to be bolded.", _
Title:="SPECIFY RANGE", Type:=8)
On Error GoTo 0

If rRange Is Nothing Then
Exit Sub
End If

For Each ws In Sheets
If ws.Visible = xlSheetVisible Then
ws.Range("Z1").Value = ws.Range(rRange.Cells(1).Address).Value
End If
Next ws

InValidEntry:
If Err = 13 Then
MsgBox "Not a valid input. " & "Please retry."
End If

End Sub

 

[Dave Peterson]

I dropped this portion from the code I suggested. You don't need it.

InValidEntry:
If Err = 13 Then
MsgBox "Not a valid input. " & "Please retry."
End If

Yep, that's it!! Thank you so much!!
Here is the final version...

Sub StartHere()

Dim rRange As Range
Dim ws As Worksheet

Set rRange = Nothing
On Error Resume Next
Set rRange = Application.InputBox(Prompt:= _
"Please select a range with your Mouse to be bolded.", _
Title:="SPECIFY RANGE", Type:=8)
On Error GoTo 0

If rRange Is Nothing Then
Exit Sub
End If

For Each ws In Sheets
If ws.Visible = xlSheetVisible Then
ws.Range("Z1").Value = ws.Range(rRange.Cells(1).Address).Value
End If
Next ws

InValidEntry:
If Err = 13 Then
MsgBox "Not a valid input. " & "Please retry."
End If

End Sub