active cell issue...
For Iloop = 12 To 15
Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
Cells(Iloop - 5, "B") = Cells(ActRow, Iloop + 2)
Next Iloop
when Iloop = 12 on the first trip through the loop, it will do this
Range("A7").value = Range("N2").Value
iloop - 5 = 12 - 5 = 7; Cells(7,"A") is A7
iloop + 2 = 14; Cells(2,14) is N2 column 14 is the N column.
you should be able to figure out the rest.
--
Regards,
Tom Ogilvy
"Murph" wrote in message
...
quick question... what does the "Iloop-5" do in the second part of the
loop
here? I understand the columns are being removed.. does the - 5 eliminate
a
group of 5 whitespaces?
For Iloop = 1 To 6
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(ActRow, Iloop + 2)
Next Iloop
For Iloop = 12 To 15
Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
Cells(Iloop - 5, "B") = Cells(ActRow, Iloop + 2)
Next Iloop
"Tom Ogilvy" wrote:
no need to change the selection, just don't depend on it
change
ActRow = ActiveCell.Row
to
ActRow = rng.row
This assumes you have your shapes on the row you want to work with.
--
Regards,
Tom Ogilvy
"Murph" wrote in message
...
I appreciate everyone's assistance first off.
I have a macro inside of an autoshape. My issue is that the macro
relies
on
the cell that is currently selected to gather information. So if the
cell
one
row down from the macro is selected I get the wrong information. Way
too
much
room for user error.
Can I set the macro to make the cell that the autoshape is located
within
the active cell? So when the autoshape is clicked it automatically
makes
that
cell the selected cell?
the current macro reads like this....
Sub Barcode()
Dim ActRow As Integer
Dim Iloop As Integer
Dim shp As Shape
Dim rng As Range
Set shp = ActiveSheet.Shapes(Application.Caller)
Set rng = shp.TopLeftCell.Offset(0, 1)
rng.Value = Now
Application.ScreenUpdating = False
ActRow = ActiveCell.Row
Columns("A:B").Insert
For Iloop = 1 To 6
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(ActRow, Iloop + 2)
Next Iloop
For Iloop = 12 To 15
Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
Cells(Iloop - 5, "B") = Cells(ActRow, Iloop + 2)
Next Iloop
Worksheets("Counts").Rows.RowHeight = 40
With Worksheets("Counts").Rows(10)
.RowHeight = .RowHeight * 3
End With
With Worksheets("Counts").Columns("A")
.ColumnWidth = .ColumnWidth * 5
End With
With Worksheets("Counts").Columns("B")
.ColumnWidth = .ColumnWidth * 8
End With
With Worksheets("Counts").Range("A1:B9")
.Font.Size = 30
With Worksheets("Counts").Range("B10")
.Font.Size = 160
End With
End With
Worksheets("Counts").Range("B10").Font.Name = "Free 3 of 9"
Range("A1:B15").PrintOut Copies:=1, Collate:=True
Worksheets("Counts").Rows.RowHeight = 25
Columns("A:B").Delete
Application.ScreenUpdating = False
End Sub
|