Referencing Cell Location
 

I have imported data from a .txt file. The data is all located in column A
and already in a proper format. I need the macro to do the following:

Read each cell... when it finds the date, the cell below should be set as
the reference cell. The value is not important. Copy specified ranges of
cells and pastespecial - transpose next to the reference cell. Below is what
I have so far. This works, but the problem lies with the absolute reference
to range B3 & E4. I will need to run this on 30,000 lines referencing offset
cells from a new reference point each time a date is found.

If I could set the cell below a date equal to the cell location and then
activate the cell for and each paste, I think that would work, but I can't
figure it out.

Brand new to VBA.

Thanks,
Adam

Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(14, 0)).copy
Range("b3").Select
Selection.PasteSpecial Paste:=xlAll, Transpose:=True

Range(ActiveCell.Offset(15, 0), ActiveCell.Offset(25, 0).Select
Selection.copy
Range("e4").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True

Referencing Cell Location
 

Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(14, 0)).copy
ActiveCell.offset(2,1)Select <added offset
Selection.PasteSpecial Paste:=xlAll, Transpose:=True

Range(ActiveCell.Offset(15, 0), ActiveCell.Offset(25, 0).Select
Selection.copy
ActiveCell.offset(3,4).Select <added offset
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True

"watchtower" wrote:

I have imported data from a .txt file. The data is all located in column A
and already in a proper format. I need the macro to do the following:

Read each cell... when it finds the date, the cell below should be set as
the reference cell. The value is not important. Copy specified ranges of
cells and pastespecial - transpose next to the reference cell. Below is what
I have so far. This works, but the problem lies with the absolute reference
to range B3 & E4. I will need to run this on 30,000 lines referencing offset
cells from a new reference point each time a date is found.

If I could set the cell below a date equal to the cell location and then
activate the cell for and each paste, I think that would work, but I can't
figure it out.

Brand new to VBA.

Thanks,
Adam

Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(14, 0)).copy
Range("b3").Select
Selection.PasteSpecial Paste:=xlAll, Transpose:=True

Range(ActiveCell.Offset(15, 0), ActiveCell.Offset(25, 0).Select
Selection.copy
Range("e4").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True

Referencing Cell Location
 

Thanks, Joel.

The problem with that is the active cell is no longer the original reference
below the date cell. After the paste, you have an active range and must
select the cell again.

Adam

"Joel" wrote:

Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(14, 0)).copy
ActiveCell.offset(2,1)Select <added offset
Selection.PasteSpecial Paste:=xlAll, Transpose:=True

Range(ActiveCell.Offset(15, 0), ActiveCell.Offset(25, 0).Select
Selection.copy
ActiveCell.offset(3,4).Select <added offset
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True

"watchtower" wrote:

I have imported data from a .txt file. The data is all located in column A
and already in a proper format. I need the macro to do the following:

Read each cell... when it finds the date, the cell below should be set as
the reference cell. The value is not important. Copy specified ranges of
cells and pastespecial - transpose next to the reference cell. Below is what
I have so far. This works, but the problem lies with the absolute reference
to range B3 & E4. I will need to run this on 30,000 lines referencing offset
cells from a new reference point each time a date is found.

If I could set the cell below a date equal to the cell location and then
activate the cell for and each paste, I think that would work, but I can't
figure it out.

Brand new to VBA.

Thanks,
Adam

Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(14, 0)).copy
Range("b3").Select
Selection.PasteSpecial Paste:=xlAll, Transpose:=True

Range(ActiveCell.Offset(15, 0), ActiveCell.Offset(25, 0).Select
Selection.copy
Range("e4").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True

Referencing Cell Location
 

You can set a variable to the activecell before the operation, then reference
this cell latter in the macro.

set Oldcell = activecell
Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(14, 0)).copy
OldCell.offset(2,1)Select <added offset
Selection.PasteSpecial Paste:=xlAll, Transpose:=True

Range(OldCell.Offset(15, 0), ActiveCell.Offset(25, 0).Select
Selection.copy
OldCell.offset(3,4).Select <added offset
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True



"watchtower" wrote:

Thanks, Joel.

The problem with that is the active cell is no longer the original reference
below the date cell. After the paste, you have an active range and must
select the cell again.

Adam

"Joel" wrote:

Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(14, 0)).copy
ActiveCell.offset(2,1)Select <added offset
Selection.PasteSpecial Paste:=xlAll, Transpose:=True

Range(ActiveCell.Offset(15, 0), ActiveCell.Offset(25, 0).Select
Selection.copy
ActiveCell.offset(3,4).Select <added offset
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True

"watchtower" wrote:

I have imported data from a .txt file. The data is all located in column A
and already in a proper format. I need the macro to do the following:

Read each cell... when it finds the date, the cell below should be set as
the reference cell. The value is not important. Copy specified ranges of
cells and pastespecial - transpose next to the reference cell. Below is what
I have so far. This works, but the problem lies with the absolute reference
to range B3 & E4. I will need to run this on 30,000 lines referencing offset
cells from a new reference point each time a date is found.

If I could set the cell below a date equal to the cell location and then
activate the cell for and each paste, I think that would work, but I can't
figure it out.

Brand new to VBA.

Thanks,
Adam

Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(14, 0)).copy
Range("b3").Select
Selection.PasteSpecial Paste:=xlAll, Transpose:=True

Range(ActiveCell.Offset(15, 0), ActiveCell.Offset(25, 0).Select
Selection.copy
Range("e4").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True