Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find first cell in range and expand range -VBA
Need help... I'm pulling my hair out on this.
In VBA, I have a user form with different textbox. I need, when there is something entered in the box (lets say the first one - textbox1), excel to browse through a apecific non-contiguous range ( Range("A1:C1,E1:G1,A4:C4,E4:G4,A7:C7,E7:G7,A10:C10 ,E10:G10") ) And find the first empty cell. Change the value of this cell and the next 9 (10 total) by the value of a variable ("NoLot1") I tried many different things, nothing worked. HELP PLEEEEEAAAASSSSE! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find first cell in range and expand range -VBA
This should work provided I understood the question correctly
Private Sub blank() Dim usr As Variant usr = InputBox.Value Range("a1:c1,E1:G1,C4:A4,E4:G4,A7:C7,E7:G7,A10:C10 ,E10:G10").Select If ActiveCell.Value = Empty Then GoTo calc Else: Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase _ :=False, SearchFormat:=False).Activate GoTo calc End If calc: ActiveCell = ActiveCell + usr For i = 1 To 9 ActiveCell.Offset(0, 1).Activate ActiveCell = ActiveCell + usr Next i End Sub "Caméléon" wrote: Need help... I'm pulling my hair out on this. In VBA, I have a user form with different textbox. I need, when there is something entered in the box (lets say the first one - textbox1), excel to browse through a apecific non-contiguous range ( Range("A1:C1,E1:G1,A4:C4,E4:G4,A7:C7,E7:G7,A10:C10 ,E10:G10") ) And find the first empty cell. Change the value of this cell and the next 9 (10 total) by the value of a variable ("NoLot1") I tried many different things, nothing worked. HELP PLEEEEEAAAASSSSE! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find first cell in range and expand range -VBA
Thanks DMoney,
Almost.. still 2 little bugs to fix. Here is what I ended up with: ======= Public NoLot1 as String --- Private Sub NoLotBox1_Change() NoLot1 = NoLotBox1.Value If NoLotBox1.Value < "" Then fill Else End If End Sub ---- Private Sub fill() Dim rng As Range Set rng = Range("A1:C1,E1:G1,C4:A4,E4:G4,A7:C7,E7:G7,A10:C10 ,E10:G10") Range("A1:C1,E1:G1,C4:A4,E4:G4,A7:C7,E7:G7,A10:C10 ,E10:G10").Select If ActiveCell.Value = Empty Then GoTo DÉBUT Else Selection.Find(What:="", After:=ActiveCell, LookIn:=xlValues).Activate GoTo DÉBUT End If DÉBUT: ActiveCell = ActiveCell + NoLot1 For i = 1 To 9 ActiveCell.Offset(0, 1).Activate ActiveCell = ActiveCell + NoLot1 Next i End Sub ====== OK, now 1) This manage to find the first cell and put in the value, but when it copies it 9 times, it copies it to the next 9 contiguous cells (not the next 9 cells in the range...) (it should not write anything in column d, and after G1 continue to A4...) 2) When I enter something in the input box, each key I enter trigger the search, so if I try to enter "war", it's gonna put w in the first 10 cells, a in the next 10 and r in the next 10. 3) How can I set a range value so I can reuse it. I've tried: Dim rng as Range Set rng = Range("A1:C1,E1:G1,C4:A4,E4:G4,A7:C7") When I want to use it, like: Range(rng).Select or With Range (rng) let's say, I get errors messages. Thanks a lot *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find first cell in range and expand range -VBA
for problem 1 use below and expand on the code for the remaining columns.
Hopefully you will catch on to the logic idea and improve the codes efficiency. for problem 2, if u can assign your variable to a cell and a button to execute the code. Sub Macro1() Dim USR As Variant USR = Range("k1").Value Range("a1:c1,E1:G1,C4:A4,E4:G4,A7:C7,E7:G7,A10:C10 ,E10:G10").Select If ActiveCell.Value = Empty Then ActiveCell = ActiveCell + USR GoTo calc Else: Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate ActiveCell = ActiveCell + USR GoTo calc End If calc: If Cells.Column = 1 Then Call calc1 ActiveCell.Offset(0, 2).Activate ActiveCell = ActiveCell + USR Call calc1 ActiveCell.Offset(3, -6).Activate ActiveCell = ActiveCell + USR Call calc1 ActiveCell.Offset(0, 2).Activate ActiveCell = ActiveCell + USR ElseIf Cells.Column = 2 Then ActiveCell = ActiveCell + USR ActiveCell.Offset(0, 1).Activate ActiveCell = ActiveCell + USR ActiveCell.Offset(0, 2).Activate Call calc1 ActiveCell.Offset(3, -6).Activate ActiveCell = ActiveCell + USR Call calc1 ActiveCell.Offset(0, 2).Activate ActiveCell = ActiveCell + USR ActiveCell.Offset(0, 1).Activate ActiveCell = ActiveCell + USR End If End Sub Public Sub calc1() Dim USR As Variant USR = 1 For i = 1 To 2 ActiveCell.Offset(0, 1).Activate ActiveCell = ActiveCell + USR Next i End Sub "Camilion" wrote: Thanks DMoney, Almost.. still 2 little bugs to fix. Here is what I ended up with: ======= Public NoLot1 as String --- Private Sub NoLotBox1_Change() NoLot1 = NoLotBox1.Value If NoLotBox1.Value < "" Then fill Else End If End Sub ---- Private Sub fill() Dim rng As Range Set rng = Range("A1:C1,E1:G1,C4:A4,E4:G4,A7:C7,E7:G7,A10:C10 ,E10:G10") Range("A1:C1,E1:G1,C4:A4,E4:G4,A7:C7,E7:G7,A10:C10 ,E10:G10").Select If ActiveCell.Value = Empty Then GoTo DIBUT Else Selection.Find(What:="", After:=ActiveCell, LookIn:=xlValues).Activate GoTo DIBUT End If DIBUT: ActiveCell = ActiveCell + NoLot1 For i = 1 To 9 ActiveCell.Offset(0, 1).Activate ActiveCell = ActiveCell + NoLot1 Next i End Sub ====== OK, now 1) This manage to find the first cell and put in the value, but when it copies it 9 times, it copies it to the next 9 contiguous cells (not the next 9 cells in the range...) (it should not write anything in column d, and after G1 continue to A4...) 2) When I enter something in the input box, each key I enter trigger the search, so if I try to enter "war", it's gonna put w in the first 10 cells, a in the next 10 and r in the next 10. 3) How can I set a range value so I can reuse it. I've tried: Dim rng as Range Set rng = Range("A1:C1,E1:G1,C4:A4,E4:G4,A7:C7") When I want to use it, like: Range(rng).Select or With Range (rng) let's say, I get errors messages. Thanks a lot *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Last cell in Range when range is date format | Excel Discussion (Misc queries) | |||
Range that expand | Excel Discussion (Misc queries) | |||
Cannot Expand Named Range - when size of the Range exceeds | Excel Discussion (Misc queries) | |||
Expand Range | Excel Programming | |||
Range.Find returns cell outside of range when range set to single cell | Excel Programming |