Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping Problem
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 cant 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 -- RyGuy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping Problem
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 wrote: 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 cant 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 -- RyGuy -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping Problem
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!!! -- RyGuy "Dave Peterson" wrote: 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 wrote: 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 -- RyGuy -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping Problem
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 ryguy7272 wrote: 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!!! -- RyGuy "Dave Peterson" wrote: 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 wrote: 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 -- RyGuy -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping Problem
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 -- RyGuy "Dave Peterson" wrote: 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 ryguy7272 wrote: 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!!! -- RyGuy "Dave Peterson" wrote: 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 wrote: 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 -- RyGuy -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping Problem
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 ryguy7272 wrote: 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 -- RyGuy "Dave Peterson" wrote: 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 ryguy7272 wrote: 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!!! -- RyGuy "Dave Peterson" wrote: 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 wrote: 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 -- RyGuy -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looping Problem | Excel Programming | |||
Looping problem........ | Excel Programming | |||
Looping problem | Excel Programming | |||
If Then Else looping problem | Excel Programming | |||
Looping Problem | Excel Programming |