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!