|
Conditional select range
Hi there,
I have no VBA-knowledge whatshowever. I was wondering if the following would be possible: Say: In A2:A500 I have names filled in: Bob, Jen, Tom ,.... Can I select Cells in column H:K when "Jen" is in that row? eg. A10 = "Jen" select range H10:K10 A67 = "Jen" select range H67:K67 and every other "row-"range when my name appears in the first Column? (Would it be possible to match the range (A2:A500) with a name I give in in an Inputbox? SO that I do not have to change the name every time in the code itself?) Hm many questions... All help really appreciated!! Jen |
Conditional select range
hi,
are we talking multiple selects? if so, how many? once selected....then what? Hm many questions... FSt1 "Jen" wrote: Hi there, I have no VBA-knowledge whatshowever. I was wondering if the following would be possible: Say: In A2:A500 I have names filled in: Bob, Jen, Tom ,.... Can I select Cells in column H:K when "Jen" is in that row? eg. A10 = "Jen" select range H10:K10 A67 = "Jen" select range H67:K67 and every other "row-"range when my name appears in the first Column? (Would it be possible to match the range (A2:A500) with a name I give in in an Inputbox? SO that I do not have to change the name every time in the code itself?) Hm many questions... All help really appreciated!! Jen |
Conditional select range
Sub SelectCells()
On Error Resume Next iRow = Application.Match("Jen",Columns(1),0) On Error Goto 0 If iRow 0 Then Cells(iRow,"H").Resize(,4).Select End If End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jen" wrote in message oups.com... Hi there, I have no VBA-knowledge whatshowever. I was wondering if the following would be possible: Say: In A2:A500 I have names filled in: Bob, Jen, Tom ,.... Can I select Cells in column H:K when "Jen" is in that row? eg. A10 = "Jen" select range H10:K10 A67 = "Jen" select range H67:K67 and every other "row-"range when my name appears in the first Column? (Would it be possible to match the range (A2:A500) with a name I give in in an Inputbox? SO that I do not have to change the name every time in the code itself?) Hm many questions... All help really appreciated!! Jen |
Conditional select range
This will do the selection, but unless you add some
code, where the MsgBox is, all it will do is walk down Column A doing selections and nothing else. lr = Cells(Rows.Count).End(xlUp).Row For Each i In Range("A2:A" & lr) If i = "Jan" Then Range ("H" & i & ":K" & i).Select End If MsgBox "H Through K of this row selected" Next "Jen" wrote: Hi there, I have no VBA-knowledge whatshowever. I was wondering if the following would be possible: Say: In A2:A500 I have names filled in: Bob, Jen, Tom ,.... Can I select Cells in column H:K when "Jen" is in that row? eg. A10 = "Jen" select range H10:K10 A67 = "Jen" select range H67:K67 and every other "row-"range when my name appears in the first Column? (Would it be possible to match the range (A2:A500) with a name I give in in an Inputbox? SO that I do not have to change the name every time in the code itself?) Hm many questions... All help really appreciated!! Jen |
Conditional select range
I gave you some bad code the
lr = Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To lr If Cells(i, 1) = "Jen" Then Range(Cells(i, 8), Cells(i, 11)).Select End If MsgBox "Cells H through K selected this row" Next This will work a lot better. It assumes a header row 1. "Jen" wrote: Hi there, I have no VBA-knowledge whatshowever. I was wondering if the following would be possible: Say: In A2:A500 I have names filled in: Bob, Jen, Tom ,.... Can I select Cells in column H:K when "Jen" is in that row? eg. A10 = "Jen" select range H10:K10 A67 = "Jen" select range H67:K67 and every other "row-"range when my name appears in the first Column? (Would it be possible to match the range (A2:A500) with a name I give in in an Inputbox? SO that I do not have to change the name every time in the code itself?) Hm many questions... All help really appreciated!! Jen |
Conditional select range
On Jun 1, 2:40 am, JLGWhiz wrote:
I gave you some bad code the lr = Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To lr If Cells(i, 1) = "Jen" Then Range(Cells(i, 8), Cells(i, 11)).Select End If MsgBox "Cells H through K selected this row" Next This will work a lot better. It assumes a header row 1. "Jen" wrote: Hi there, I have no VBA-knowledge whatshowever. I was wondering if the following would be possible: Say: In A2:A500 I have names filled in: Bob, Jen, Tom ,.... Can I select Cells in column H:K when "Jen" is in that row? eg. A10 = "Jen" select range H10:K10 A67 = "Jen" select range H67:K67 and every other "row-"range when my name appears in the first Column? (Would it be possible to match the range (A2:A500) with a name I give in in an Inputbox? SO that I do not have to change the name every time in the code itself?) Hm many questions... All help really appreciated!! Jen- Hide quoted text - - Show quoted text - Hi All, Thank you so much for your help! I was hoping though that I could have a multiple selection though ... So that all my instances of "Jen" found in the first column would select all the ranges in column H:K, instead of just the first instance found? You see I have difficulties to even properly describe what i "want". :) Hopefully this is possible?! Thanks again, Jen |
Conditional select range
Have you thought about using Data|Filter|autofilter instead of selecting the
range? That way, your data will be visible and you won't have to do any scrolling. But if you want: Option Explicit Sub testme() Dim myName As String Dim RngToSearch As Range Dim myFoundRng As Range Dim FoundCell As Range Dim FirstAddress As String myName = Application.InputBox(Prompt:="Enter a name") If myName = "" Then Exit Sub End If With ActiveSheet Set RngToSearch = .Range("a:a") End With FirstAddress = "" Set myFoundRng = Nothing With RngToSearch Set FoundCell = .Cells.Find(what:=myName, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ searchorder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then MsgBox myName & " wasn't found!" Else FirstAddress = FoundCell.Address Set myFoundRng = FoundCell.Offset(0, 7).Resize(1, 4) 'columns H:K Do Set FoundCell = .FindNext(after:=FoundCell) If FoundCell.Address = FirstAddress Then Exit Do End If Set myFoundRng = Union(myFoundRng, _ FoundCell.Offset(0, 7).Resize(1, 4)) Loop myFoundRng.Select End If End With End Sub Jen wrote: On Jun 1, 2:40 am, JLGWhiz wrote: I gave you some bad code the lr = Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To lr If Cells(i, 1) = "Jen" Then Range(Cells(i, 8), Cells(i, 11)).Select End If MsgBox "Cells H through K selected this row" Next This will work a lot better. It assumes a header row 1. "Jen" wrote: Hi there, I have no VBA-knowledge whatshowever. I was wondering if the following would be possible: Say: In A2:A500 I have names filled in: Bob, Jen, Tom ,.... Can I select Cells in column H:K when "Jen" is in that row? eg. A10 = "Jen" select range H10:K10 A67 = "Jen" select range H67:K67 and every other "row-"range when my name appears in the first Column? (Would it be possible to match the range (A2:A500) with a name I give in in an Inputbox? SO that I do not have to change the name every time in the code itself?) Hm many questions... All help really appreciated!! Jen- Hide quoted text - - Show quoted text - Hi All, Thank you so much for your help! I was hoping though that I could have a multiple selection though ... So that all my instances of "Jen" found in the first column would select all the ranges in column H:K, instead of just the first instance found? You see I have difficulties to even properly describe what i "want". :) Hopefully this is possible?! Thanks again, Jen -- Dave Peterson |
Conditional select range
On Jun 1, 2:20 pm, Dave Peterson wrote:
Have you thought about using Data|Filter|autofilter instead of selecting the range? That way, your data will be visible and you won't have to do any scrolling. But if you want: Option Explicit Sub testme() Dim myName As String Dim RngToSearch As Range Dim myFoundRng As Range Dim FoundCell As Range Dim FirstAddress As String myName = Application.InputBox(Prompt:="Enter a name") If myName = "" Then Exit Sub End If With ActiveSheet Set RngToSearch = .Range("a:a") End With FirstAddress = "" Set myFoundRng = Nothing With RngToSearch Set FoundCell = .Cells.Find(what:=myName, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ searchorder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then MsgBox myName & " wasn't found!" Else FirstAddress = FoundCell.Address Set myFoundRng = FoundCell.Offset(0, 7).Resize(1, 4) 'columns H:K Do Set FoundCell = .FindNext(after:=FoundCell) If FoundCell.Address = FirstAddress Then Exit Do End If Set myFoundRng = Union(myFoundRng, _ FoundCell.Offset(0, 7).Resize(1, 4)) Loop myFoundRng.Select End If End With End Sub Jen wrote: On Jun 1, 2:40 am, JLGWhiz wrote: I gave you some bad code the lr = Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To lr If Cells(i, 1) = "Jen" Then Range(Cells(i, 8), Cells(i, 11)).Select End If MsgBox "Cells H through K selected this row" Next This will work a lot better. It assumes a header row 1. "Jen" wrote: Hi there, I have no VBA-knowledge whatshowever. I was wondering if the following would be possible: Say: In A2:A500 I have names filled in: Bob, Jen, Tom ,.... Can I select Cells in column H:K when "Jen" is in that row? eg. A10 = "Jen" select range H10:K10 A67 = "Jen" select range H67:K67 and every other "row-"range when my name appears in the first Column? (Would it be possible to match the range (A2:A500) with a name I give in in an Inputbox? SO that I do not have to change the name every time in the code itself?) Hm many questions... All help really appreciated!! Jen- Hide quoted text - - Show quoted text - Hi All, Thank you so much for your help! I was hoping though that I could have a multiple selection though ... So that all my instances of "Jen" found in the first column would select all the ranges in column H:K, instead of just the first instance found? You see I have difficulties to even properly describe what i "want". :) Hopefully this is possible?! Thanks again, Jen -- Dave Peterson- Hide quoted text - - Show quoted text - Hi Dave, That's exactly what it should do! A charm! There is 1 more thing though .... What I wanted to do is: 1. I copy a cell with a number eg. 5 2. then run the macro to select my cells 3. Paste Special Operation Multiply on the cells your macro selected. But it seems that when I run the macro... my copied value does not retain in teh memory...? Would it be possible to keep that? (or could the macro at the end of the selection process prompt me for a value to multiply it with?) Hope you can help once again!! :) Jen |
Conditional select range
Are you trying to paste special|add a value in a cell or are you trying to
increment that range by an amount you want to enter--like the way you entered the name? I guessed that you wanted to specify the value--not the cell: Option Explicit Sub testme() Dim myName As String Dim RngToSearch As Range Dim myFoundRng As Range Dim FoundCell As Range Dim FirstAddress As String Dim DummyCell As Range Dim QtyToAdd As Double myName = InputBox(Prompt:="Enter a name") If myName = "" Then Exit Sub End If QtyToAdd = Application.InputBox(Prompt:="Enter a quantity to add", Type:=1) If QtyToAdd = 0 Then Exit Sub End If With ActiveSheet Set RngToSearch = .Range("a:a") Set DummyCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1) End With FirstAddress = "" Set myFoundRng = Nothing With RngToSearch Set FoundCell = .Cells.Find(what:=myName, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ searchorder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then MsgBox myName & " wasn't found!" Else FirstAddress = FoundCell.Address Set myFoundRng = FoundCell.Offset(0, 7).Resize(1, 4) 'columns H:K Do Set FoundCell = .FindNext(after:=FoundCell) If FoundCell.Address = FirstAddress Then Exit Do End If Set myFoundRng = Union(myFoundRng, _ FoundCell.Offset(0, 7).Resize(1, 4)) Loop With DummyCell .Value = QtyToAdd .Copy End With myFoundRng.PasteSpecial Paste:=xlPasteValues, _ operation:=xlPasteSpecialOperationAdd DummyCell.ClearContents 'no need to select unless you really want 'myFoundRng.Select End If End With End Sub Jen wrote: <<snipped Hi Dave, That's exactly what it should do! A charm! There is 1 more thing though .... What I wanted to do is: 1. I copy a cell with a number eg. 5 2. then run the macro to select my cells 3. Paste Special Operation Multiply on the cells your macro selected. But it seems that when I run the macro... my copied value does not retain in teh memory...? Would it be possible to keep that? (or could the macro at the end of the selection process prompt me for a value to multiply it with?) Hope you can help once again!! :) Jen -- Dave Peterson |
Conditional select range
On Jun 1, 5:30 pm, Dave Peterson wrote:
Are you trying to paste special|add a value in a cell or are you trying to increment that range by an amount you want to enter--like the way you entered the name? I guessed that you wanted to specify the value--not the cell: Option Explicit Sub testme() Dim myName As String Dim RngToSearch As Range Dim myFoundRng As Range Dim FoundCell As Range Dim FirstAddress As String Dim DummyCell As Range Dim QtyToAdd As Double myName = InputBox(Prompt:="Enter a name") If myName = "" Then Exit Sub End If QtyToAdd = Application.InputBox(Prompt:="Enter a quantity to add", Type:=1) If QtyToAdd = 0 Then Exit Sub End If With ActiveSheet Set RngToSearch = .Range("a:a") Set DummyCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1) End With FirstAddress = "" Set myFoundRng = Nothing With RngToSearch Set FoundCell = .Cells.Find(what:=myName, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ searchorder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then MsgBox myName & " wasn't found!" Else FirstAddress = FoundCell.Address Set myFoundRng = FoundCell.Offset(0, 7).Resize(1, 4) 'columns H:K Do Set FoundCell = .FindNext(after:=FoundCell) If FoundCell.Address = FirstAddress Then Exit Do End If Set myFoundRng = Union(myFoundRng, _ FoundCell.Offset(0, 7).Resize(1, 4)) Loop With DummyCell .Value = QtyToAdd .Copy End With myFoundRng.PasteSpecial Paste:=xlPasteValues, _ operation:=xlPasteSpecialOperationAdd DummyCell.ClearContents 'no need to select unless you really want 'myFoundRng.Select End If End With End Sub Jen wrote: <<snipped Hi Dave, That's exactly what it should do! A charm! There is 1 more thing though .... What I wanted to do is: 1. I copy a cell with a number eg. 5 2. then run the macro to select my cells 3. Paste Special Operation Multiply on the cells your macro selected. But it seems that when I run the macro... my copied value does not retain in teh memory...? Would it be possible to keep that? (or could the macro at the end of the selection process prompt me for a value to multiply it with?) Hope you can help once again!! :) Jen -- Dave Peterson- Hide quoted text - - Show quoted text - Fantastic Dave, You're my hero! You've made my weekend! Cheers, sooooo :)))))))) |
Conditional select range
On Jun 1, 6:46 pm, Jen wrote:
On Jun 1, 5:30 pm, Dave Peterson wrote: Are you trying to paste special|add a value in a cell or are you trying to increment that range by an amount you want to enter--like the way you entered the name? I guessed that you wanted to specify the value--not the cell: Option Explicit Sub testme() Dim myName As String Dim RngToSearch As Range Dim myFoundRng As Range Dim FoundCell As Range Dim FirstAddress As String Dim DummyCell As Range Dim QtyToAdd As Double myName = InputBox(Prompt:="Enter a name") If myName = "" Then Exit Sub End If QtyToAdd = Application.InputBox(Prompt:="Enter a quantity to add", Type:=1) If QtyToAdd = 0 Then Exit Sub End If With ActiveSheet Set RngToSearch = .Range("a:a") Set DummyCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1) End With FirstAddress = "" Set myFoundRng = Nothing With RngToSearch Set FoundCell = .Cells.Find(what:=myName, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ searchorder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then MsgBox myName & " wasn't found!" Else FirstAddress = FoundCell.Address Set myFoundRng = FoundCell.Offset(0, 7).Resize(1, 4) 'columns H:K Do Set FoundCell = .FindNext(after:=FoundCell) If FoundCell.Address = FirstAddress Then Exit Do End If Set myFoundRng = Union(myFoundRng, _ FoundCell.Offset(0, 7).Resize(1, 4)) Loop With DummyCell .Value = QtyToAdd .Copy End With myFoundRng.PasteSpecial Paste:=xlPasteValues, _ operation:=xlPasteSpecialOperationAdd DummyCell.ClearContents 'no need to select unless you really want 'myFoundRng.Select End If End With End Sub Jen wrote: <<snipped Hi Dave, That's exactly what it should do! A charm! There is 1 more thing though .... What I wanted to do is: 1. I copy a cell with a number eg. 5 2. then run the macro to select my cells 3. Paste Special Operation Multiply on the cells your macro selected. But it seems that when I run the macro... my copied value does not retain in teh memory...? Would it be possible to keep that? (or could the macro at the end of the selection process prompt me for a value to multiply it with?) Hope you can help once again!! :) Jen -- Dave Peterson- Hide quoted text - - Show quoted text - Fantastic Dave, You're my hero! You've made my weekend! Cheers, sooooo :))))))))- Hide quoted text - - Show quoted text - Hi Dave, I have been tampering a little bit around with your code and made some small adjustments which I Googled in teh newsgroup here. I would love to change still something on it: Sub testme() Dim myName As String Dim OffsetToRight As String Dim nrColumnsToSelect As String Dim RngToSearch As Range Dim myFoundRng As Range Dim FoundCell As Range Dim FirstAddress As String Dim QtyToMultiply As Double Dim DummyCell As Range On Error Resume Next myName = Application.InputBox(prompt:="SELECT or write the ''name'' in the range that has to match?") If myName = "" Then Exit Sub End If With ActiveSheet Set RngToSearch = Application.InputBox("Where is the range to apply the search on?", Type:=8) Set DummyCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1) End With OffsetToRight = Application.InputBox(prompt:="Start selection how many columns to the right?", Type:=1) nrColumnsToSelect = Application.InputBox(prompt:="How many columns to select?", Type:=1) QtyToMultiply = Application.InputBox(prompt:="Enter or Select the number to MULTIPLY with", Type:=1) If QtyToMultiply = 0 Then Exit Sub End If FirstAddress = "" Set myFoundRng = Nothing With RngToSearch Set FoundCell = .Cells.Find(what:=myName, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ searchorder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then MsgBox myName & " wasn't found!" Else FirstAddress = FoundCell.Address Set myFoundRng = FoundCell.Offset(0, OffsetToRight).Resize(1, nrColumnsToSelect) Do Set FoundCell = .FindNext(after:=FoundCell) If FoundCell.Address = FirstAddress Then Exit Do End If Set myFoundRng = Union(myFoundRng, _ FoundCell.Offset(0, OffsetToRight).Resize(1, nrColumnsToSelect)) Loop With DummyCell .Value = QtyToMultiply .Copy End With myFoundRng.PasteSpecial Paste:=xlPasteValues, _ operation:=xlPasteSpecialOperationMultiply DummyCell.ClearContents myFoundRng.Select End If End With End Sub Would it be possible that you SUGGEST in the inputbox a certain range already? eg from G5:Till_Last_Row_In_G-_column? Set RngToSearch = Application.InputBox("Where is the range to apply the search on?", Type:=8) I think I am going completely overboard here but I love to see what's possible and what not... I start to really like VBA :))) Jennnnnnnn |
Conditional select range
If you know that the range is always in column G, but the number of rows vary,
you could use something like: Option Explicit Sub testme() Dim RngToSearch As Range Dim myDefaultRng As Range With ActiveSheet Set myDefaultRng = .Range("G5", .Cells(.Rows.Count, "G").End(xlUp)) End With Set RngToSearch = Nothing On Error Resume Next Set RngToSearch = Application.InputBox _ ("Where is the range to apply the search on?", _ Default:=myDefaultRng.Address(external:=True), Type:=8) On Error GoTo 0 If RngToSearch Is Nothing Then Exit Sub 'user hit cancel End If 'you may not want this portion With RngToSearch Set RngToSearch = Intersect(.Areas(1).EntireRow, .Parent.Range("g:g")) End With MsgBox RngToSearch.Address(external:=True) End Sub Jen wrote: On Jun 1, 6:46 pm, Jen wrote: On Jun 1, 5:30 pm, Dave Peterson wrote: Are you trying to paste special|add a value in a cell or are you trying to increment that range by an amount you want to enter--like the way you entered the name? I guessed that you wanted to specify the value--not the cell: Option Explicit Sub testme() Dim myName As String Dim RngToSearch As Range Dim myFoundRng As Range Dim FoundCell As Range Dim FirstAddress As String Dim DummyCell As Range Dim QtyToAdd As Double myName = InputBox(Prompt:="Enter a name") If myName = "" Then Exit Sub End If QtyToAdd = Application.InputBox(Prompt:="Enter a quantity to add", Type:=1) If QtyToAdd = 0 Then Exit Sub End If With ActiveSheet Set RngToSearch = .Range("a:a") Set DummyCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1) End With FirstAddress = "" Set myFoundRng = Nothing With RngToSearch Set FoundCell = .Cells.Find(what:=myName, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ searchorder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then MsgBox myName & " wasn't found!" Else FirstAddress = FoundCell.Address Set myFoundRng = FoundCell.Offset(0, 7).Resize(1, 4) 'columns H:K Do Set FoundCell = .FindNext(after:=FoundCell) If FoundCell.Address = FirstAddress Then Exit Do End If Set myFoundRng = Union(myFoundRng, _ FoundCell.Offset(0, 7).Resize(1, 4)) Loop With DummyCell .Value = QtyToAdd .Copy End With myFoundRng.PasteSpecial Paste:=xlPasteValues, _ operation:=xlPasteSpecialOperationAdd DummyCell.ClearContents 'no need to select unless you really want 'myFoundRng.Select End If End With End Sub Jen wrote: <<snipped Hi Dave, That's exactly what it should do! A charm! There is 1 more thing though .... What I wanted to do is: 1. I copy a cell with a number eg. 5 2. then run the macro to select my cells 3. Paste Special Operation Multiply on the cells your macro selected. But it seems that when I run the macro... my copied value does not retain in teh memory...? Would it be possible to keep that? (or could the macro at the end of the selection process prompt me for a value to multiply it with?) Hope you can help once again!! :) Jen -- Dave Peterson- Hide quoted text - - Show quoted text - Fantastic Dave, You're my hero! You've made my weekend! Cheers, sooooo :))))))))- Hide quoted text - - Show quoted text - Hi Dave, I have been tampering a little bit around with your code and made some small adjustments which I Googled in teh newsgroup here. I would love to change still something on it: Sub testme() Dim myName As String Dim OffsetToRight As String Dim nrColumnsToSelect As String Dim RngToSearch As Range Dim myFoundRng As Range Dim FoundCell As Range Dim FirstAddress As String Dim QtyToMultiply As Double Dim DummyCell As Range On Error Resume Next myName = Application.InputBox(prompt:="SELECT or write the ''name'' in the range that has to match?") If myName = "" Then Exit Sub End If With ActiveSheet Set RngToSearch = Application.InputBox("Where is the range to apply the search on?", Type:=8) Set DummyCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1) End With OffsetToRight = Application.InputBox(prompt:="Start selection how many columns to the right?", Type:=1) nrColumnsToSelect = Application.InputBox(prompt:="How many columns to select?", Type:=1) QtyToMultiply = Application.InputBox(prompt:="Enter or Select the number to MULTIPLY with", Type:=1) If QtyToMultiply = 0 Then Exit Sub End If FirstAddress = "" Set myFoundRng = Nothing With RngToSearch Set FoundCell = .Cells.Find(what:=myName, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ searchorder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then MsgBox myName & " wasn't found!" Else FirstAddress = FoundCell.Address Set myFoundRng = FoundCell.Offset(0, OffsetToRight).Resize(1, nrColumnsToSelect) Do Set FoundCell = .FindNext(after:=FoundCell) If FoundCell.Address = FirstAddress Then Exit Do End If Set myFoundRng = Union(myFoundRng, _ FoundCell.Offset(0, OffsetToRight).Resize(1, nrColumnsToSelect)) Loop With DummyCell .Value = QtyToMultiply .Copy End With myFoundRng.PasteSpecial Paste:=xlPasteValues, _ operation:=xlPasteSpecialOperationMultiply DummyCell.ClearContents myFoundRng.Select End If End With End Sub Would it be possible that you SUGGEST in the inputbox a certain range already? eg from G5:Till_Last_Row_In_G-_column? Set RngToSearch = Application.InputBox("Where is the range to apply the search on?", Type:=8) I think I am going completely overboard here but I love to see what's possible and what not... I start to really like VBA :))) Jennnnnnnn -- Dave Peterson |
Conditional select range
On Jun 1, 8:52 pm, Dave Peterson wrote:
If you know that the range is always in column G, but the number of rows vary, you could use something like: Option Explicit Sub testme() Dim RngToSearch As Range Dim myDefaultRng As Range With ActiveSheet Set myDefaultRng = .Range("G5", .Cells(.Rows.Count, "G").End(xlUp)) End With Set RngToSearch = Nothing On Error Resume Next Set RngToSearch = Application.InputBox _ ("Where is the range to apply the search on?", _ Default:=myDefaultRng.Address(external:=True), Type:=8) On Error GoTo 0 If RngToSearch Is Nothing Then Exit Sub 'user hit cancel End If 'you may not want this portion With RngToSearch Set RngToSearch = Intersect(.Areas(1).EntireRow, .Parent.Range("g:g")) End With MsgBox RngToSearch.Address(external:=True) End Sub Jen wrote: On Jun 1, 6:46 pm, Jen wrote: On Jun 1, 5:30 pm, Dave Peterson wrote: Are you trying to paste special|add a value in a cell or are you trying to increment that range by an amount you want to enter--like the way you entered the name? I guessed that you wanted to specify the value--not the cell: Option Explicit Sub testme() Dim myName As String Dim RngToSearch As Range Dim myFoundRng As Range Dim FoundCell As Range Dim FirstAddress As String Dim DummyCell As Range Dim QtyToAdd As Double myName = InputBox(Prompt:="Enter a name") If myName = "" Then Exit Sub End If QtyToAdd = Application.InputBox(Prompt:="Enter a quantity to add", Type:=1) If QtyToAdd = 0 Then Exit Sub End If With ActiveSheet Set RngToSearch = .Range("a:a") Set DummyCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1) End With FirstAddress = "" Set myFoundRng = Nothing With RngToSearch Set FoundCell = .Cells.Find(what:=myName, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ searchorder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then MsgBox myName & " wasn't found!" Else FirstAddress = FoundCell.Address Set myFoundRng = FoundCell.Offset(0, 7).Resize(1, 4) 'columns H:K Do Set FoundCell = .FindNext(after:=FoundCell) If FoundCell.Address = FirstAddress Then Exit Do End If Set myFoundRng = Union(myFoundRng, _ FoundCell.Offset(0, 7).Resize(1, 4)) Loop With DummyCell .Value = QtyToAdd .Copy End With myFoundRng.PasteSpecial Paste:=xlPasteValues, _ operation:=xlPasteSpecialOperationAdd DummyCell.ClearContents 'no need to select unless you really want 'myFoundRng.Select End If End With End Sub Jen wrote: <<snipped Hi Dave, That's exactly what it should do! A charm! There is 1 more thing though .... What I wanted to do is: 1. I copy a cell with a number eg. 5 2. then run the macro to select my cells 3. Paste Special Operation Multiply on the cells your macro selected. But it seems that when I run the macro... my copied value does not retain in teh memory...? Would it be possible to keep that? (or could the macro at the end of the selection process prompt me for a value to multiply it with?) Hope you can help once again!! :) Jen -- Dave Peterson- Hide quoted text - - Show quoted text - Fantastic Dave, You're my hero! You've made my weekend! Cheers, sooooo :))))))))- Hide quoted text - - Show quoted text - Hi Dave, I have been tampering a little bit around with your code and made some small adjustments which I Googled in teh newsgroup here. I would love to change still something on it: Sub testme() Dim myName As String Dim OffsetToRight As String Dim nrColumnsToSelect As String Dim RngToSearch As Range Dim myFoundRng As Range Dim FoundCell As Range Dim FirstAddress As String Dim QtyToMultiply As Double Dim DummyCell As Range On Error Resume Next myName = Application.InputBox(prompt:="SELECT or write the ''name'' in the range that has to match?") If myName = "" Then Exit Sub End If With ActiveSheet Set RngToSearch = Application.InputBox("Where is the range to apply the search on?", Type:=8) Set DummyCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1) End With OffsetToRight = Application.InputBox(prompt:="Start selection how many columns to the right?", Type:=1) nrColumnsToSelect = Application.InputBox(prompt:="How many columns to select?", Type:=1) QtyToMultiply = Application.InputBox(prompt:="Enter or Select the number to MULTIPLY with", Type:=1) If QtyToMultiply = 0 Then Exit Sub End If FirstAddress = "" Set myFoundRng = Nothing With RngToSearch Set FoundCell = .Cells.Find(what:=myName, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ searchorder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then MsgBox myName & " wasn't found!" Else FirstAddress = FoundCell.Address Set myFoundRng = FoundCell.Offset(0, OffsetToRight).Resize(1, nrColumnsToSelect) Do Set FoundCell = .FindNext(after:=FoundCell) If FoundCell.Address = FirstAddress Then Exit Do End If Set myFoundRng = Union(myFoundRng, _ FoundCell.Offset(0, OffsetToRight).Resize(1, nrColumnsToSelect)) Loop With DummyCell .Value = QtyToMultiply .Copy End With myFoundRng.PasteSpecial Paste:=xlPasteValues, _ operation:=xlPasteSpecialOperationMultiply DummyCell.ClearContents myFoundRng.Select End If End With End Sub Would it be possible that you SUGGEST in the inputbox a certain range already? eg from G5:Till_Last_Row_In_G-_column? Set RngToSearch = Application.InputBox("Where is the range to apply the search on?", Type:=8) I think I am going completely overboard here but I love to see what's possible and what not... I start to really like VBA :))) Jennnnnnnn -- Dave Peterson- Hide quoted text - - Show quoted text - Man, man, man, This is just A W E S O M E! ... Nothing but respect! Thanks for the help ...!! I am sure I´ll be hung up on VBA for a while...probably ´coming back with more crazy questions :)) Jen |
Conditional select range
On Jun 1, 8:52 pm, Dave Peterson wrote:
If you know that the range is always in column G, but the number of rows vary, you could use something like: Option Explicit Sub testme() Dim RngToSearch As Range Dim myDefaultRng As Range With ActiveSheet Set myDefaultRng = .Range("G5", .Cells(.Rows.Count, "G").End(xlUp)) End With Set RngToSearch = Nothing On Error Resume Next Set RngToSearch = Application.InputBox _ ("Where is the range to apply the search on?", _ Default:=myDefaultRng.Address(external:=True), Type:=8) On Error GoTo 0 If RngToSearch Is Nothing Then Exit Sub 'user hit cancel End If 'you may not want this portion With RngToSearch Set RngToSearch = Intersect(.Areas(1).EntireRow, .Parent.Range("g:g")) End With MsgBox RngToSearch.Address(external:=True) End Sub Jen wrote: On Jun 1, 6:46 pm, Jen wrote: On Jun 1, 5:30 pm, Dave Peterson wrote: Are you trying to paste special|add a value in a cell or are you trying to increment that range by an amount you want to enter--like the way you entered the name? I guessed that you wanted to specify the value--not the cell: Option Explicit Sub testme() Dim myName As String Dim RngToSearch As Range Dim myFoundRng As Range Dim FoundCell As Range Dim FirstAddress As String Dim DummyCell As Range Dim QtyToAdd As Double myName = InputBox(Prompt:="Enter a name") If myName = "" Then Exit Sub End If QtyToAdd = Application.InputBox(Prompt:="Enter a quantity to add", Type:=1) If QtyToAdd = 0 Then Exit Sub End If With ActiveSheet Set RngToSearch = .Range("a:a") Set DummyCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1) End With FirstAddress = "" Set myFoundRng = Nothing With RngToSearch Set FoundCell = .Cells.Find(what:=myName, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ searchorder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then MsgBox myName & " wasn't found!" Else FirstAddress = FoundCell.Address Set myFoundRng = FoundCell.Offset(0, 7).Resize(1, 4) 'columns H:K Do Set FoundCell = .FindNext(after:=FoundCell) If FoundCell.Address = FirstAddress Then Exit Do End If Set myFoundRng = Union(myFoundRng, _ FoundCell.Offset(0, 7).Resize(1, 4)) Loop With DummyCell .Value = QtyToAdd .Copy End With myFoundRng.PasteSpecial Paste:=xlPasteValues, _ operation:=xlPasteSpecialOperationAdd DummyCell.ClearContents 'no need to select unless you really want 'myFoundRng.Select End If End With End Sub Jen wrote: <<snipped Hi Dave, That's exactly what it should do! A charm! There is 1 more thing though .... What I wanted to do is: 1. I copy a cell with a number eg. 5 2. then run the macro to select my cells 3. Paste Special Operation Multiply on the cells your macro selected. But it seems that when I run the macro... my copied value does not retain in teh memory...? Would it be possible to keep that? (or could the macro at the end of the selection process prompt me for a value to multiply it with?) Hope you can help once again!! :) Jen -- Dave Peterson- Hide quoted text - - Show quoted text - Fantastic Dave, You're my hero! You've made my weekend! Cheers, sooooo :))))))))- Hide quoted text - - Show quoted text - Hi Dave, I have been tampering a little bit around with your code and made some small adjustments which I Googled in teh newsgroup here. I would love to change still something on it: Sub testme() Dim myName As String Dim OffsetToRight As String Dim nrColumnsToSelect As String Dim RngToSearch As Range Dim myFoundRng As Range Dim FoundCell As Range Dim FirstAddress As String Dim QtyToMultiply As Double Dim DummyCell As Range On Error Resume Next myName = Application.InputBox(prompt:="SELECT or write the ''name'' in the range that has to match?") If myName = "" Then Exit Sub End If With ActiveSheet Set RngToSearch = Application.InputBox("Where is the range to apply the search on?", Type:=8) Set DummyCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1) End With OffsetToRight = Application.InputBox(prompt:="Start selection how many columns to the right?", Type:=1) nrColumnsToSelect = Application.InputBox(prompt:="How many columns to select?", Type:=1) QtyToMultiply = Application.InputBox(prompt:="Enter or Select the number to MULTIPLY with", Type:=1) If QtyToMultiply = 0 Then Exit Sub End If FirstAddress = "" Set myFoundRng = Nothing With RngToSearch Set FoundCell = .Cells.Find(what:=myName, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ searchorder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then MsgBox myName & " wasn't found!" Else FirstAddress = FoundCell.Address Set myFoundRng = FoundCell.Offset(0, OffsetToRight).Resize(1, nrColumnsToSelect) Do Set FoundCell = .FindNext(after:=FoundCell) If FoundCell.Address = FirstAddress Then Exit Do End If Set myFoundRng = Union(myFoundRng, _ FoundCell.Offset(0, OffsetToRight).Resize(1, nrColumnsToSelect)) Loop With DummyCell .Value = QtyToMultiply .Copy End With myFoundRng.PasteSpecial Paste:=xlPasteValues, _ operation:=xlPasteSpecialOperationMultiply DummyCell.ClearContents myFoundRng.Select End If End With End Sub Would it be possible that you SUGGEST in the inputbox a certain range already? eg from G5:Till_Last_Row_In_G-_column? Set RngToSearch = Application.InputBox("Where is the range to apply the search on?", Type:=8) I think I am going completely overboard here but I love to see what's possible and what not... I start to really like VBA :))) Jennnnnnnn -- Dave Peterson- Hide quoted text - - Show quoted text - Hi Dave, I think to have understoood your code more or less. Just your last part I do not get fully. 'you may not want this portion With RngToSearch Set RngToSearch = Intersect(.Areas(1).EntireRow, .Parent.Range("A:A")) End With What does it do? Jen |
Conditional select range
The with/end with is a nice way to save typing. Each properties/methods that
starts with a dot belongs to the object in that With statement. You can select multiple areas in that application.inputbox--just like selecting a range, then ctrl-clicking on another range. rngtosearch.Areas(1) is the first area--just in case the user selected multiple areas. So rngtosearch.areas(1).entirerow is the equivalent of selecting a single range (manually), then hitting the shift-spacebar (all the columns in that area's rows are selected). rngtosearch.parent is the worksheet that owns that range. (The .parent of the worksheet is the workbook. The .parent of the workbook is the application.) rngtosearch.parent.parent.name is the workbook's name And rngtosearch.parent.range("a:a") is column A of that worksheet. Intersect(rng1, rng2) is just the cells that are common to both. Since you're using the whole row (.entirerow) and the whole column (.range("a:A")), you know that there is an intersection. It's the stuff in column A that's also in the rows that were selected. If you know that you want stuff out of column A, you could use this to let the user select the rows--and they won't have to worry about being careful enough to select column A--your code does that part. ps. You changed from G:G to A:A. Was that on purpose? Jen wrote: <<snipped - Show quoted text - Hi Dave, I think to have understoood your code more or less. Just your last part I do not get fully. 'you may not want this portion With RngToSearch Set RngToSearch = Intersect(.Areas(1).EntireRow, .Parent.Range("A:A")) End With What does it do? Jen -- Dave Peterson |
Conditional select range
On Jun 2, 12:10 am, Dave Peterson wrote:
The with/end with is a nice way to save typing. Each properties/methods that starts with a dot belongs to the object in that With statement. You can select multiple areas in that application.inputbox--just like selecting a range, then ctrl-clicking on another range. rngtosearch.Areas(1) is the first area--just in case the user selected multiple areas. So rngtosearch.areas(1).entirerow is the equivalent of selecting a single range (manually), then hitting the shift-spacebar (all the columns in that area's rows are selected). rngtosearch.parent is the worksheet that owns that range. (The .parent of the worksheet is the workbook. The .parent of the workbook is the application.) rngtosearch.parent.parent.name is the workbook's name And rngtosearch.parent.range("a:a") is column A of that worksheet. Intersect(rng1, rng2) is just the cells that are common to both. Since you're using the whole row (.entirerow) and the whole column (.range("a:A")), you know that there is an intersection. It's the stuff in column A that's also in the rows that were selected. If you know that you want stuff out of column A, you could use this to let the user select the rows--and they won't have to worry about being careful enough to select column A--your code does that part. ps. You changed from G:G to A:A. Was that on purpose? Jen wrote: <<snipped - Show quoted text - Hi Dave, I think to have understoood your code more or less. Just your last part I do not get fully. 'you may not want this portion With RngToSearch Set RngToSearch = Intersect(.Areas(1).EntireRow, .Parent.Range("A:A")) End With What does it do? Jen -- Dave Peterson- Hide quoted text - - Show quoted text - Hi Dave, -THANK you SOO much for the explanation. Sounds "logic", but it is deviously cool!!! -I am toying around with the VBA on some "practise" sheets, where I practise on column A. I will apply it in the end on column G in my "real live" file. Anyway, I "advanced" a bit further on your code and this is my result: Sub MultiplyValuesCombination() Dim myDefaultRng As Range Dim RngToSearch As Range, HLookupRange As Range Dim ConvRange As Range, v As Variant Dim cell As Variant Dim cell1 As Variant Dim OffsetToRight As Variant Dim nrColumnsToSelect As Variant Dim myDefaultHLOOKUPRng As Range With ActiveSheet Set myDefaultRng = .Range("A5", .Cells(.Rows.Count, "A").End(xlUp)) End With Set RngToSearch = Nothing On Error Resume Next Set RngToSearch = Application.InputBox _ ("Where is the range to apply the search on?", _ Default:=myDefaultRng.Address(external:=True), Type:=8) On Error GoTo 0 If RngToSearch Is Nothing Then Exit Sub 'user hit cancel End If OffsetToRight = Application.InputBox _ ("Start selection how many columns to the Right?", _ Default:=4, Type:=1) On Error GoTo 0 If OffsetToRight = False Then Exit Sub 'user hit cancel End If nrColumnsToSelect = Application.InputBox _ ("HOW MANY Columns to select?", _ Default:=5, Type:=1) On Error GoTo 0 If nrColumnsToSelect = False Then Exit Sub 'user hit cancel End If 'you may not want this portion With RngToSearch Set RngToSearch = Intersect(.Areas(1).EntireRow, .Parent.Range("A:A")) End With 'MsgBox RngToSearch.Address(external:=True) 'On Error Resume Next Set myDefaultHLOOKUPRng = Range("conv") Set HLookupRange = Application.InputBox _ ("Where is the range with the CONVERSION RATIOS (Max 2rows & ratio in 2nd!)?", _ Default:=myDefaultHLOOKUPRng.Address(external:=Tru e), Type:=8) On Error GoTo 0 If HLookupRange Is Nothing Then Exit Sub 'user hit cancel End If For Each cell In RngToSearch v = Application.HLookup(cell, HLookupRange, 2, 0) If Not IsError(v) Then If IsNumeric(v) Then Set ConvRng = cell.Offset(0, OffsetToRight).Resize(1, nrColumnsToSelect) For Each cell1 In ConvRng cell1.Value = cell1.Value * v Next cell1 End If End If Next cell End Sub I have a question related with this part: Set myDefaultHLOOKUPRng = Range("conv") Set HLookupRange = Application.InputBox _ ("Where is the range with the CONVERSION RATIOS (Max 2rows & ratio in 2nd!)?", _ Default:=myDefaultHLOOKUPRng.Address(external:=Tru e), Type:=8) On Error GoTo 0 If HLookupRange Is Nothing Then Exit Sub 'user hit cancel End If 1. Normally the ranged Name "conv" DOES exist ... but in case it does NOT I would just like to select the range but now it errs out! If I put an "On error resume next" above it, it will just exit the sub ... but I would like that I can still select my range although the "conv" name does not exist. 2. I have repeated plenty of code blocks. I assume that could be written in a neater way ...? (Well, "for a first day in the office"... ;) ) The "On error goto 0" are all over the place eg. ... do I need them? what it means? Jen, first day on VBA :) |
Conditional select range
(Top posted on purpose <vbg)
First, when you ran your code (with "Option Explicit" at the top, it wouldn't run, right? You declared ConvRange as a range, but later in your code, you used ConvRng. In this case, it probably wouldn't have mattered--since you used convrng consistently after that. But if you had used ConvRange somewhere else and really wanted to use whatever ConvRng was holding, it may have taken a while to find that bug. By using "Option Explcit", you must declare your variables. It seems like more work at the beginning, but you'll soon find the benefits are too great not to do this. (intellisense and autocomplete always help me.) Next, you'll see lots of code posted on these newsgroups that look like: Dim myObject as someobject 'not real objects--just showing the syntax set myObject = nothing on error resume next 'the next line can cause an error, but I know that. 'So Ms. VBA, don't bother blowing up or telling me about it! set myObject = someobjecthere 'I'm done with the line that could cause the error. 'Ms. VBA, please go back to handling all errors until I tell you otherwise. 'And that's just the syntax that gives back error handling to excel. On error goto 0 'Test to see if it was successful: if myObject is nothing then 'that previous Set failed and what I was looking for doesn't exist. else 'yep, it was there end if ======== For example: dim testwks as worksheet '...a bunch of code here set testwks = nothing on error resume next set testwks = activeworkbook.worksheets("Jen's Worksheet") on error goto 0 if testwks is nothing then msgbox "that worksheet doesn't exist! else msgbox testwks.range("a1").value 'just doing something end if This is how I changed your code: Option Explicit Sub MultiplyValuesCombination() 'I like one line per variable. I find that I can find/fix things quicker. 'but that's a personal preference only. Dim myDefaultRng As Range Dim RngToSearch As Range Dim HLookupRange As Range Dim ConvRng As Range Dim v As Variant Dim cell As Variant Dim cell1 As Variant Dim OffsetToRight As Variant Dim nrColumnsToSelect As Variant Dim myDefaultHLOOKUPRng As Range With ActiveSheet Set myDefaultRng = .Range("A5", .Cells(.Rows.Count, "A").End(xlUp)) End With Set RngToSearch = Nothing On Error Resume Next Set RngToSearch = Application.InputBox _ ("Where is the range to apply the search on?", _ Default:=myDefaultRng.Address(external:=True), _ Type:=8) On Error GoTo 0 If RngToSearch Is Nothing Then Exit Sub 'user hit cancel End If OffsetToRight = Application.InputBox _ ("Start selection how many columns to the Right?", _ Default:=4, Type:=1) If OffsetToRight = False Then Exit Sub 'user hit cancel End If nrColumnsToSelect = Application.InputBox _ ("HOW MANY Columns to select?", _ Default:=5, Type:=1) If nrColumnsToSelect = False Then Exit Sub 'user hit cancel End If With RngToSearch Set RngToSearch = Intersect(.Areas(1).EntireRow, .Parent.Range("A:A")) End With Set myDefaultHLOOKUPRng = Nothing On Error Resume Next 'I added the activesheet here! Set myDefaultHLOOKUPRng = ActiveSheet.Range("conv") On Error GoTo 0 If myDefaultHLOOKUPRng Is Nothing Then Exit Sub End If Set HLookupRange = Application.InputBox _ ("Where is the range with the" & _ " CONVERSION RATIOS (Max 2rows & ratio in 2nd!)?", _ Default:=myDefaultHLOOKUPRng.Address(external:=Tru e), Type:=8) If HLookupRange Is Nothing Then Exit Sub 'user hit cancel End If For Each cell In RngToSearch v = Application.HLookup(cell, HLookupRange, 2, 0) If Not IsError(v) Then If IsNumeric(v) Then Set ConvRng _ = cell.Offset(0, OffsetToRight).Resize(1, nrColumnsToSelect) 'I like convrng.cells, but in most cases it won't matter. 'but being explicit is nicer than not For Each cell1 In ConvRng.Cells cell1.Value = cell1.Value * v Next cell1 End If End If Next cell End Sub ========= Right now you're asking the user 4 questions. That's stretching the limits of clicking ok for me. Imagine if you had to answer each of the settings under File|Page setup by using an inputbox. The way you'd clean this kind of stuff up is to design a userform that can ask those same questions. In fact, for the questions that return numbers, you could use a scrollbar or spinner or even a textbox if you wanted. Someday (probably not day one!), you'll want to learn how to do this. Check Debra Dalgleish's site: http://contextures.com/xlUserForm01.html and these articles by Peter: http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx And when you think you want to look at books... Debra Dalgleish has a list of books at her site: http://www.contextures.com/xlbooks.html John Walkenbach's is a nice one to start with. See if you can find them in your local bookstore/internet site and you can choose what one you like best. Jen wrote: <<snipped Hi Dave, -THANK you SOO much for the explanation. Sounds "logic", but it is deviously cool!!! -I am toying around with the VBA on some "practise" sheets, where I practise on column A. I will apply it in the end on column G in my "real live" file. Anyway, I "advanced" a bit further on your code and this is my result: Sub MultiplyValuesCombination() Dim myDefaultRng As Range Dim RngToSearch As Range, HLookupRange As Range Dim ConvRange As Range, v As Variant Dim cell As Variant Dim cell1 As Variant Dim OffsetToRight As Variant Dim nrColumnsToSelect As Variant Dim myDefaultHLOOKUPRng As Range With ActiveSheet Set myDefaultRng = .Range("A5", .Cells(.Rows.Count, "A").End(xlUp)) End With Set RngToSearch = Nothing On Error Resume Next Set RngToSearch = Application.InputBox _ ("Where is the range to apply the search on?", _ Default:=myDefaultRng.Address(external:=True), Type:=8) On Error GoTo 0 If RngToSearch Is Nothing Then Exit Sub 'user hit cancel End If OffsetToRight = Application.InputBox _ ("Start selection how many columns to the Right?", _ Default:=4, Type:=1) On Error GoTo 0 If OffsetToRight = False Then Exit Sub 'user hit cancel End If nrColumnsToSelect = Application.InputBox _ ("HOW MANY Columns to select?", _ Default:=5, Type:=1) On Error GoTo 0 If nrColumnsToSelect = False Then Exit Sub 'user hit cancel End If 'you may not want this portion With RngToSearch Set RngToSearch = Intersect(.Areas(1).EntireRow, .Parent.Range("A:A")) End With 'MsgBox RngToSearch.Address(external:=True) 'On Error Resume Next Set myDefaultHLOOKUPRng = Range("conv") Set HLookupRange = Application.InputBox _ ("Where is the range with the CONVERSION RATIOS (Max 2rows & ratio in 2nd!)?", _ Default:=myDefaultHLOOKUPRng.Address(external:=Tru e), Type:=8) On Error GoTo 0 If HLookupRange Is Nothing Then Exit Sub 'user hit cancel End If For Each cell In RngToSearch v = Application.HLookup(cell, HLookupRange, 2, 0) If Not IsError(v) Then If IsNumeric(v) Then Set ConvRng = cell.Offset(0, OffsetToRight).Resize(1, nrColumnsToSelect) For Each cell1 In ConvRng cell1.Value = cell1.Value * v Next cell1 End If End If Next cell End Sub I have a question related with this part: Set myDefaultHLOOKUPRng = Range("conv") Set HLookupRange = Application.InputBox _ ("Where is the range with the CONVERSION RATIOS (Max 2rows & ratio in 2nd!)?", _ Default:=myDefaultHLOOKUPRng.Address(external:=Tru e), Type:=8) On Error GoTo 0 If HLookupRange Is Nothing Then Exit Sub 'user hit cancel End If 1. Normally the ranged Name "conv" DOES exist ... but in case it does NOT I would just like to select the range but now it errs out! If I put an "On error resume next" above it, it will just exit the sub ... but I would like that I can still select my range although the "conv" name does not exist. 2. I have repeated plenty of code blocks. I assume that could be written in a neater way ...? (Well, "for a first day in the office"... ;) ) The "On error goto 0" are all over the place eg. ... do I need them? what it means? Jen, first day on VBA :) -- Dave Peterson |
Conditional select range
On Jun 2, 2:09 am, Dave Peterson wrote:
(Top posted on purpose <vbg) First, when you ran your code (with "Option Explicit" at the top, it wouldn't run, right? You declared ConvRange as a range, but later in your code, you used ConvRng. In this case, it probably wouldn't have mattered--since you used convrng consistently after that. But if you had used ConvRange somewhere else and really wanted to use whatever ConvRng was holding, it may have taken a while to find that bug. By using "Option Explcit", you must declare your variables. It seems like more work at the beginning, but you'll soon find the benefits are too great not to do this. (intellisense and autocomplete always help me.) Next, you'll see lots of code posted on these newsgroups that look like: Dim myObject as someobject 'not real objects--just showing the syntax set myObject = nothing on error resume next 'the next line can cause an error, but I know that. 'So Ms. VBA, don't bother blowing up or telling me about it! set myObject = someobjecthere 'I'm done with the line that could cause the error. 'Ms. VBA, please go back to handling all errors until I tell you otherwise. 'And that's just the syntax that gives back error handling to excel. On error goto 0 'Test to see if it was successful: if myObject is nothing then 'that previous Set failed and what I was looking for doesn't exist. else 'yep, it was there end if ======== For example: dim testwks as worksheet '...a bunch of code here set testwks = nothing on error resume next set testwks = activeworkbook.worksheets("Jen's Worksheet") on error goto 0 if testwks is nothing then msgbox "that worksheet doesn't exist! else msgbox testwks.range("a1").value 'just doing something end if This is how I changed your code: Option Explicit Sub MultiplyValuesCombination() 'I like one line per variable. I find that I can find/fix things quicker. 'but that's a personal preference only. Dim myDefaultRng As Range Dim RngToSearch As Range Dim HLookupRange As Range Dim ConvRng As Range Dim v As Variant Dim cell As Variant Dim cell1 As Variant Dim OffsetToRight As Variant Dim nrColumnsToSelect As Variant Dim myDefaultHLOOKUPRng As Range With ActiveSheet Set myDefaultRng = .Range("A5", .Cells(.Rows.Count, "A").End(xlUp)) End With Set RngToSearch = Nothing On Error Resume Next Set RngToSearch = Application.InputBox _ ("Where is the range to apply the search on?", _ Default:=myDefaultRng.Address(external:=True), _ Type:=8) On Error GoTo 0 If RngToSearch Is Nothing Then Exit Sub 'user hit cancel End If OffsetToRight = Application.InputBox _ ("Start selection how many columns to the Right?", _ Default:=4, Type:=1) If OffsetToRight = False Then Exit Sub 'user hit cancel End If nrColumnsToSelect = Application.InputBox _ ("HOW MANY Columns to select?", _ Default:=5, Type:=1) If nrColumnsToSelect = False Then Exit Sub 'user hit cancel End If With RngToSearch Set RngToSearch = Intersect(.Areas(1).EntireRow, .Parent.Range("A:A")) End With Set myDefaultHLOOKUPRng = Nothing On Error Resume Next 'I added the activesheet here! Set myDefaultHLOOKUPRng = ActiveSheet.Range("conv") On Error GoTo 0 If myDefaultHLOOKUPRng Is Nothing Then Exit Sub End If Set HLookupRange = Application.InputBox _ ("Where is the range with the" & _ " CONVERSION RATIOS (Max 2rows & ratio in 2nd!)?", _ Default:=myDefaultHLOOKUPRng.Address(external:=Tru e), Type:=8) If HLookupRange Is Nothing Then Exit Sub 'user hit cancel End If For Each cell In RngToSearch v = Application.HLookup(cell, HLookupRange, 2, 0) If Not IsError(v) Then If IsNumeric(v) Then Set ConvRng _ = cell.Offset(0, OffsetToRight).Resize(1, nrColumnsToSelect) 'I like convrng.cells, but in most cases it won't matter. 'but being explicit is nicer than not For Each cell1 In ConvRng.Cells cell1.Value = cell1.Value * v Next cell1 End If End If Next cell End Sub ========= Right now you're asking the user 4 questions. That's stretching the limits of clicking ok for me. Imagine if you had to answer each of the settings under File|Page setup by using an inputbox. The way you'd clean this kind of stuff up is to design a userform that can ask those same questions. In fact, for the questions that return numbers, you could use a scrollbar or spinner or even a textbox if you wanted. Someday (probably not day one!), you'll want to learn how to do this. Check Debra Dalgleish's site:http://contextures.com/xlUserForm01.html and these articles by Peter:http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx And when you think you want to look at books... Debra Dalgleish has a list of books at her site:http://www.contextures.com/xlbooks.html John Walkenbach's is a nice one to start with. See if you can find them in your local bookstore/internet site and you can choose what one you like best. Jen wrote: <<snipped Hi Dave, -THANK you SOO much for the explanation. Sounds "logic", but it is deviously cool!!! -I am toying around with the VBA on some "practise" sheets, where I practise on column A. I will apply it in the end on column G in my "real live" file. Anyway, I "advanced" a bit further on your code and this is my result: Sub MultiplyValuesCombination() Dim myDefaultRng As Range Dim RngToSearch As Range, HLookupRange As Range Dim ConvRange As Range, v As Variant Dim cell As Variant Dim cell1 As Variant Dim OffsetToRight As Variant Dim nrColumnsToSelect As Variant Dim myDefaultHLOOKUPRng As Range With ActiveSheet Set myDefaultRng = .Range("A5", .Cells(.Rows.Count, "A").End(xlUp)) End With Set RngToSearch = Nothing On Error Resume Next Set RngToSearch = Application.InputBox _ ("Where is the range to apply the search on?", _ Default:=myDefaultRng.Address(external:=True), Type:=8) On Error GoTo 0 If RngToSearch Is Nothing Then Exit Sub 'user hit cancel End If OffsetToRight = Application.InputBox _ ("Start selection how many columns to the Right?", _ Default:=4, Type:=1) On Error GoTo 0 If OffsetToRight = False Then Exit Sub 'user hit cancel End If nrColumnsToSelect = Application.InputBox _ ("HOW MANY Columns to select?", _ Default:=5, Type:=1) On Error GoTo 0 If nrColumnsToSelect = False Then Exit Sub 'user hit cancel End If 'you may not want this portion With RngToSearch Set RngToSearch = Intersect(.Areas(1).EntireRow, .Parent.Range("A:A")) End With 'MsgBox RngToSearch.Address(external:=True) 'On Error Resume Next Set myDefaultHLOOKUPRng = Range("conv") Set HLookupRange = Application.InputBox _ ("Where is the range with the CONVERSION RATIOS (Max 2rows & ratio in 2nd!)?", _ Default:=myDefaultHLOOKUPRng.Address(external:=Tru e), Type:=8) On Error GoTo 0 If HLookupRange Is Nothing Then Exit Sub 'user hit cancel End If For Each cell In RngToSearch v = Application.HLookup(cell, HLookupRange, 2, 0) If Not IsError(v) Then If IsNumeric(v) Then Set ConvRng = cell.Offset(0, OffsetToRight).Resize(1, nrColumnsToSelect) For Each cell1 In ConvRng cell1.Value = cell1.Value * v Next cell1 End If End If Next cell End Sub I have a question related with this part: Set myDefaultHLOOKUPRng = Range("conv") Set HLookupRange = Application.InputBox _ ("Where is the range with the CONVERSION RATIOS (Max 2rows & ratio in 2nd!)?", _ Default:=myDefaultHLOOKUPRng.Address(external:=Tru e), Type:=8) On Error GoTo 0 If HLookupRange Is Nothing Then Exit Sub 'user hit cancel End If 1. Normally the ranged Name "conv" DOES exist ... but in case it does NOT I would just like to select the range but now it errs out! If I put an "On error resume next" above it, it will just exit the sub ... but I would like that I can still select my range although the "conv" name does not exist. 2. I have repeated plenty of code blocks. I assume that could be written in a neater way ...? (Well, "for a first day in the office"... ;) ) The "On error goto 0" are all over the place eg. ... do I need them? what it means? Jen, first day on VBA :) -- Dave Peterson- Hide quoted text - - Show quoted text - Hi Dave, Back, from a short night of sleep ... I dreamed about VBA and it's opportunities! Thank you so much for the "step-by-step" explanation, probably you must have explained this already over-and-over-again to numerous people. Biggest respect for that!! Thanks a lot. When I see the code you've written, I am thinking: "that's easy enough", "That's super-logic...". "Adjusting" someone else's snippets will be my best way to learn about VBA and it's syntax I guess. I don't see myself writing an extensive macro from scratch annnny time soon. & I was wondering whether you keep some sort of syntax-/ snippets- library where you start with?... I guess not? Most likely you write VBA-syntax in the same fluent way as you speak your mother tongue? Thank you for directing me to John's and Debra's site. I'll definitely explore that extensively! About the books ...I'll soon place a bulk order ;) But I've a question about something specific the Very often I am working with data that I pull down from a database first (SAP, Essbase eg.) and then I start calculating away. Are there any books you recommend on the Topic of "connecting-Excel-to- databases", OLAPS, ODBC (whatever all these things mean...). I know I have to learn how to walk first ;) ...but dreaming about the Olympics makes you train harder to run faster. Let me see what VBA has in mind for me today, Thanks for your help and explanation, Jen :) |
Conditional select range
Top posted...
I have a history of the posts I've made to the newsgroups--so if I see a question that looks very similar (or almost exact), I'll just copy and paste. If it's slightly different, I'll tweak the existing code and post that. I don't do the database thing. You may want to start a new thread and ask there--or even search google. And by declaring my variables as the correct type and using intellisense, it gets easier to pick the property/method that I want. Jen wrote: <<snipped Hi Dave, Back, from a short night of sleep ... I dreamed about VBA and it's opportunities! Thank you so much for the "step-by-step" explanation, probably you must have explained this already over-and-over-again to numerous people. Biggest respect for that!! Thanks a lot. When I see the code you've written, I am thinking: "that's easy enough", "That's super-logic...". "Adjusting" someone else's snippets will be my best way to learn about VBA and it's syntax I guess. I don't see myself writing an extensive macro from scratch annnny time soon. & I was wondering whether you keep some sort of syntax-/ snippets- library where you start with?... I guess not? Most likely you write VBA-syntax in the same fluent way as you speak your mother tongue? Thank you for directing me to John's and Debra's site. I'll definitely explore that extensively! About the books ...I'll soon place a bulk order ;) But I've a question about something specific the Very often I am working with data that I pull down from a database first (SAP, Essbase eg.) and then I start calculating away. Are there any books you recommend on the Topic of "connecting-Excel-to- databases", OLAPS, ODBC (whatever all these things mean...). I know I have to learn how to walk first ;) ...but dreaming about the Olympics makes you train harder to run faster. Let me see what VBA has in mind for me today, Thanks for your help and explanation, Jen :) -- Dave Peterson |
Conditional select range
Hi Dave,
I was using the "final" code under the weekend on some test sheets with great success. But now on my "live-data" the code does not change a thing ... I get -still / luckily- all the requests for input but the final calculation remains like untouched. As if the lookup-valuein the RngToSearch does not match with any of the HLOOKUp-values in the HLookUpRng! If I just click A5=HLookUpValue it turns TRUE though ... Any thoughts? Jen "Dave Peterson" wrote in message ... (Top posted on purpose <vbg) First, when you ran your code (with "Option Explicit" at the top, it wouldn't run, right? You declared ConvRange as a range, but later in your code, you used ConvRng. In this case, it probably wouldn't have mattered--since you used convrng consistently after that. But if you had used ConvRange somewhere else and really wanted to use whatever ConvRng was holding, it may have taken a while to find that bug. By using "Option Explcit", you must declare your variables. It seems like more work at the beginning, but you'll soon find the benefits are too great not to do this. (intellisense and autocomplete always help me.) Next, you'll see lots of code posted on these newsgroups that look like: Dim myObject as someobject 'not real objects--just showing the syntax set myObject = nothing on error resume next 'the next line can cause an error, but I know that. 'So Ms. VBA, don't bother blowing up or telling me about it! set myObject = someobjecthere 'I'm done with the line that could cause the error. 'Ms. VBA, please go back to handling all errors until I tell you otherwise. 'And that's just the syntax that gives back error handling to excel. On error goto 0 'Test to see if it was successful: if myObject is nothing then 'that previous Set failed and what I was looking for doesn't exist. else 'yep, it was there end if ======== For example: dim testwks as worksheet '...a bunch of code here set testwks = nothing on error resume next set testwks = activeworkbook.worksheets("Jen's Worksheet") on error goto 0 if testwks is nothing then msgbox "that worksheet doesn't exist! else msgbox testwks.range("a1").value 'just doing something end if This is how I changed your code: Option Explicit Sub MultiplyValuesCombination() 'I like one line per variable. I find that I can find/fix things quicker. 'but that's a personal preference only. Dim myDefaultRng As Range Dim RngToSearch As Range Dim HLookupRange As Range Dim ConvRng As Range Dim v As Variant Dim cell As Variant Dim cell1 As Variant Dim OffsetToRight As Variant Dim nrColumnsToSelect As Variant Dim myDefaultHLOOKUPRng As Range With ActiveSheet Set myDefaultRng = .Range("A5", .Cells(.Rows.Count, "A").End(xlUp)) End With Set RngToSearch = Nothing On Error Resume Next Set RngToSearch = Application.InputBox _ ("Where is the range to apply the search on?", _ Default:=myDefaultRng.Address(external:=True), _ Type:=8) On Error GoTo 0 If RngToSearch Is Nothing Then Exit Sub 'user hit cancel End If OffsetToRight = Application.InputBox _ ("Start selection how many columns to the Right?", _ Default:=4, Type:=1) If OffsetToRight = False Then Exit Sub 'user hit cancel End If nrColumnsToSelect = Application.InputBox _ ("HOW MANY Columns to select?", _ Default:=5, Type:=1) If nrColumnsToSelect = False Then Exit Sub 'user hit cancel End If With RngToSearch Set RngToSearch = Intersect(.Areas(1).EntireRow, .Parent.Range("A:A")) End With Set myDefaultHLOOKUPRng = Nothing On Error Resume Next 'I added the activesheet here! Set myDefaultHLOOKUPRng = ActiveSheet.Range("conv") On Error GoTo 0 If myDefaultHLOOKUPRng Is Nothing Then Exit Sub End If Set HLookupRange = Application.InputBox _ ("Where is the range with the" & _ " CONVERSION RATIOS (Max 2rows & ratio in 2nd!)?", _ Default:=myDefaultHLOOKUPRng.Address(external:=Tru e), Type:=8) If HLookupRange Is Nothing Then Exit Sub 'user hit cancel End If For Each cell In RngToSearch v = Application.HLookup(cell, HLookupRange, 2, 0) If Not IsError(v) Then If IsNumeric(v) Then Set ConvRng _ = cell.Offset(0, OffsetToRight).Resize(1, nrColumnsToSelect) 'I like convrng.cells, but in most cases it won't matter. 'but being explicit is nicer than not For Each cell1 In ConvRng.Cells cell1.Value = cell1.Value * v Next cell1 End If End If Next cell End Sub ========= Right now you're asking the user 4 questions. That's stretching the limits of clicking ok for me. Imagine if you had to answer each of the settings under File|Page setup by using an inputbox. The way you'd clean this kind of stuff up is to design a userform that can ask those same questions. In fact, for the questions that return numbers, you could use a scrollbar or spinner or even a textbox if you wanted. Someday (probably not day one!), you'll want to learn how to do this. Check Debra Dalgleish's site: http://contextures.com/xlUserForm01.html and these articles by Peter: http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx And when you think you want to look at books... Debra Dalgleish has a list of books at her site: http://www.contextures.com/xlbooks.html John Walkenbach's is a nice one to start with. See if you can find them in your local bookstore/internet site and you can choose what one you like best. Jen wrote: <<snipped Hi Dave, -THANK you SOO much for the explanation. Sounds "logic", but it is deviously cool!!! -I am toying around with the VBA on some "practise" sheets, where I practise on column A. I will apply it in the end on column G in my "real live" file. Anyway, I "advanced" a bit further on your code and this is my result: Sub MultiplyValuesCombination() Dim myDefaultRng As Range Dim RngToSearch As Range, HLookupRange As Range Dim ConvRange As Range, v As Variant Dim cell As Variant Dim cell1 As Variant Dim OffsetToRight As Variant Dim nrColumnsToSelect As Variant Dim myDefaultHLOOKUPRng As Range With ActiveSheet Set myDefaultRng = .Range("A5", .Cells(.Rows.Count, "A").End(xlUp)) End With Set RngToSearch = Nothing On Error Resume Next Set RngToSearch = Application.InputBox _ ("Where is the range to apply the search on?", _ Default:=myDefaultRng.Address(external:=True), Type:=8) On Error GoTo 0 If RngToSearch Is Nothing Then Exit Sub 'user hit cancel End If OffsetToRight = Application.InputBox _ ("Start selection how many columns to the Right?", _ Default:=4, Type:=1) On Error GoTo 0 If OffsetToRight = False Then Exit Sub 'user hit cancel End If nrColumnsToSelect = Application.InputBox _ ("HOW MANY Columns to select?", _ Default:=5, Type:=1) On Error GoTo 0 If nrColumnsToSelect = False Then Exit Sub 'user hit cancel End If 'you may not want this portion With RngToSearch Set RngToSearch = Intersect(.Areas(1).EntireRow, .Parent.Range("A:A")) End With 'MsgBox RngToSearch.Address(external:=True) 'On Error Resume Next Set myDefaultHLOOKUPRng = Range("conv") Set HLookupRange = Application.InputBox _ ("Where is the range with the CONVERSION RATIOS (Max 2rows & ratio in 2nd!)?", _ Default:=myDefaultHLOOKUPRng.Address(external:=Tru e), Type:=8) On Error GoTo 0 If HLookupRange Is Nothing Then Exit Sub 'user hit cancel End If For Each cell In RngToSearch v = Application.HLookup(cell, HLookupRange, 2, 0) If Not IsError(v) Then If IsNumeric(v) Then Set ConvRng = cell.Offset(0, OffsetToRight).Resize(1, nrColumnsToSelect) For Each cell1 In ConvRng cell1.Value = cell1.Value * v Next cell1 End If End If Next cell End Sub I have a question related with this part: Set myDefaultHLOOKUPRng = Range("conv") Set HLookupRange = Application.InputBox _ ("Where is the range with the CONVERSION RATIOS (Max 2rows & ratio in 2nd!)?", _ Default:=myDefaultHLOOKUPRng.Address(external:=Tru e), Type:=8) On Error GoTo 0 If HLookupRange Is Nothing Then Exit Sub 'user hit cancel End If 1. Normally the ranged Name "conv" DOES exist ... but in case it does NOT I would just like to select the range but now it errs out! If I put an "On error resume next" above it, it will just exit the sub ... but I would like that I can still select my range although the "conv" name does not exist. 2. I have repeated plenty of code blocks. I assume that could be written in a neater way ...? (Well, "for a first day in the office"... ;) ) The "On error goto 0" are all over the place eg. ... do I need them? what it means? Jen, first day on VBA :) -- Dave Peterson |
Conditional select range
I would try stepping through the code.
Get everything set up the way you need--maybe on a smaller set of data that fails. Then go to the VBE and put your cursor somewhere inside that procedure and hit F8. F8 will step through each line of code. So you can see how the code gets run. You'll want to look closely at this portion (if this is the final code): For Each cell In RngToSearch v = Application.HLookup(cell, HLookupRange, 2, 0) If Not IsError(v) Then If IsNumeric(v) Then Set ConvRng _ = cell.Offset(0, OffsetToRight).Resize(1, nrColumnsToSelect) 'I like convrng.cells, but in most cases it won't matter. 'but being explicit is nicer than not For Each cell1 In ConvRng.Cells cell1.Value = cell1.Value * v Next cell1 End If End If Next cell Maybe the value v isn't numeric??? ======= ps. There is no such thing as Final code <vbg. Jen wrote: Hi Dave, I was using the "final" code under the weekend on some test sheets with great success. But now on my "live-data" the code does not change a thing ... I get -still / luckily- all the requests for input but the final calculation remains like untouched. As if the lookup-valuein the RngToSearch does not match with any of the HLOOKUp-values in the HLookUpRng! If I just click A5=HLookUpValue it turns TRUE though ... Any thoughts? Jen "Dave Peterson" wrote in message ... (Top posted on purpose <vbg) First, when you ran your code (with "Option Explicit" at the top, it wouldn't run, right? You declared ConvRange as a range, but later in your code, you used ConvRng. In this case, it probably wouldn't have mattered--since you used convrng consistently after that. But if you had used ConvRange somewhere else and really wanted to use whatever ConvRng was holding, it may have taken a while to find that bug. By using "Option Explcit", you must declare your variables. It seems like more work at the beginning, but you'll soon find the benefits are too great not to do this. (intellisense and autocomplete always help me.) Next, you'll see lots of code posted on these newsgroups that look like: Dim myObject as someobject 'not real objects--just showing the syntax set myObject = nothing on error resume next 'the next line can cause an error, but I know that. 'So Ms. VBA, don't bother blowing up or telling me about it! set myObject = someobjecthere 'I'm done with the line that could cause the error. 'Ms. VBA, please go back to handling all errors until I tell you otherwise. 'And that's just the syntax that gives back error handling to excel. On error goto 0 'Test to see if it was successful: if myObject is nothing then 'that previous Set failed and what I was looking for doesn't exist. else 'yep, it was there end if ======== For example: dim testwks as worksheet '...a bunch of code here set testwks = nothing on error resume next set testwks = activeworkbook.worksheets("Jen's Worksheet") on error goto 0 if testwks is nothing then msgbox "that worksheet doesn't exist! else msgbox testwks.range("a1").value 'just doing something end if This is how I changed your code: Option Explicit Sub MultiplyValuesCombination() 'I like one line per variable. I find that I can find/fix things quicker. 'but that's a personal preference only. Dim myDefaultRng As Range Dim RngToSearch As Range Dim HLookupRange As Range Dim ConvRng As Range Dim v As Variant Dim cell As Variant Dim cell1 As Variant Dim OffsetToRight As Variant Dim nrColumnsToSelect As Variant Dim myDefaultHLOOKUPRng As Range With ActiveSheet Set myDefaultRng = .Range("A5", .Cells(.Rows.Count, "A").End(xlUp)) End With Set RngToSearch = Nothing On Error Resume Next Set RngToSearch = Application.InputBox _ ("Where is the range to apply the search on?", _ Default:=myDefaultRng.Address(external:=True), _ Type:=8) On Error GoTo 0 If RngToSearch Is Nothing Then Exit Sub 'user hit cancel End If OffsetToRight = Application.InputBox _ ("Start selection how many columns to the Right?", _ Default:=4, Type:=1) If OffsetToRight = False Then Exit Sub 'user hit cancel End If nrColumnsToSelect = Application.InputBox _ ("HOW MANY Columns to select?", _ Default:=5, Type:=1) If nrColumnsToSelect = False Then Exit Sub 'user hit cancel End If With RngToSearch Set RngToSearch = Intersect(.Areas(1).EntireRow, .Parent.Range("A:A")) End With Set myDefaultHLOOKUPRng = Nothing On Error Resume Next 'I added the activesheet here! Set myDefaultHLOOKUPRng = ActiveSheet.Range("conv") On Error GoTo 0 If myDefaultHLOOKUPRng Is Nothing Then Exit Sub End If Set HLookupRange = Application.InputBox _ ("Where is the range with the" & _ " CONVERSION RATIOS (Max 2rows & ratio in 2nd!)?", _ Default:=myDefaultHLOOKUPRng.Address(external:=Tru e), Type:=8) If HLookupRange Is Nothing Then Exit Sub 'user hit cancel End If For Each cell In RngToSearch v = Application.HLookup(cell, HLookupRange, 2, 0) If Not IsError(v) Then If IsNumeric(v) Then Set ConvRng _ = cell.Offset(0, OffsetToRight).Resize(1, nrColumnsToSelect) 'I like convrng.cells, but in most cases it won't matter. 'but being explicit is nicer than not For Each cell1 In ConvRng.Cells cell1.Value = cell1.Value * v Next cell1 End If End If Next cell End Sub ========= Right now you're asking the user 4 questions. That's stretching the limits of clicking ok for me. Imagine if you had to answer each of the settings under File|Page setup by using an inputbox. The way you'd clean this kind of stuff up is to design a userform that can ask those same questions. In fact, for the questions that return numbers, you could use a scrollbar or spinner or even a textbox if you wanted. Someday (probably not day one!), you'll want to learn how to do this. Check Debra Dalgleish's site: http://contextures.com/xlUserForm01.html and these articles by Peter: http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx And when you think you want to look at books... Debra Dalgleish has a list of books at her site: http://www.contextures.com/xlbooks.html John Walkenbach's is a nice one to start with. See if you can find them in your local bookstore/internet site and you can choose what one you like best. Jen wrote: <<snipped Hi Dave, -THANK you SOO much for the explanation. Sounds "logic", but it is deviously cool!!! -I am toying around with the VBA on some "practise" sheets, where I practise on column A. I will apply it in the end on column G in my "real live" file. Anyway, I "advanced" a bit further on your code and this is my result: Sub MultiplyValuesCombination() Dim myDefaultRng As Range Dim RngToSearch As Range, HLookupRange As Range Dim ConvRange As Range, v As Variant Dim cell As Variant Dim cell1 As Variant Dim OffsetToRight As Variant Dim nrColumnsToSelect As Variant Dim myDefaultHLOOKUPRng As Range With ActiveSheet Set myDefaultRng = .Range("A5", .Cells(.Rows.Count, "A").End(xlUp)) End With Set RngToSearch = Nothing On Error Resume Next Set RngToSearch = Application.InputBox _ ("Where is the range to apply the search on?", _ Default:=myDefaultRng.Address(external:=True), Type:=8) On Error GoTo 0 If RngToSearch Is Nothing Then Exit Sub 'user hit cancel End If OffsetToRight = Application.InputBox _ ("Start selection how many columns to the Right?", _ Default:=4, Type:=1) On Error GoTo 0 If OffsetToRight = False Then Exit Sub 'user hit cancel End If nrColumnsToSelect = Application.InputBox _ ("HOW MANY Columns to select?", _ Default:=5, Type:=1) On Error GoTo 0 If nrColumnsToSelect = False Then Exit Sub 'user hit cancel End If 'you may not want this portion With RngToSearch Set RngToSearch = Intersect(.Areas(1).EntireRow, .Parent.Range("A:A")) End With 'MsgBox RngToSearch.Address(external:=True) 'On Error Resume Next Set myDefaultHLOOKUPRng = Range("conv") Set HLookupRange = Application.InputBox _ ("Where is the range with the CONVERSION RATIOS (Max 2rows & ratio in 2nd!)?", _ Default:=myDefaultHLOOKUPRng.Address(external:=Tru e), Type:=8) On Error GoTo 0 If HLookupRange Is Nothing Then Exit Sub 'user hit cancel End If For Each cell In RngToSearch v = Application.HLookup(cell, HLookupRange, 2, 0) If Not IsError(v) Then If IsNumeric(v) Then Set ConvRng = cell.Offset(0, OffsetToRight).Resize(1, nrColumnsToSelect) For Each cell1 In ConvRng cell1.Value = cell1.Value * v Next cell1 End If End If Next cell End Sub I have a question related with this part: Set myDefaultHLOOKUPRng = Range("conv") Set HLookupRange = Application.InputBox _ ("Where is the range with the CONVERSION RATIOS (Max 2rows & ratio in 2nd!)?", _ Default:=myDefaultHLOOKUPRng.Address(external:=Tru e), Type:=8) On Error GoTo 0 If HLookupRange Is Nothing Then Exit Sub 'user hit cancel End If 1. Normally the ranged Name "conv" DOES exist ... but in case it does NOT I would just like to select the range but now it errs out! If I put an "On error resume next" above it, it will just exit the sub ... but I would like that I can still select my range although the "conv" name does not exist. 2. I have repeated plenty of code blocks. I assume that could be written in a neater way ...? (Well, "for a first day in the office"... ;) ) The "On error goto 0" are all over the place eg. ... do I need them? what it means? Jen, first day on VBA :) -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 07:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com