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!

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!

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!

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!