piece of code needed

[Norbert]

Hello,

I have trouble to get my macro to perform the same, even when the size
of my table changes its dimensions.

I run a report on our ERP system which gives out a txt file which I want
to change to a different layout. Therefore I import it into Excel,
determining the width of columns, deleting what is not needed and so on.

At one stage of my macro, I have an empty column A and I want to insert
a formula in each cell as far down as there are values in column B.
The number of values in column B will change every time I run the query
to get the txt file from our ERP system. Therefore my macro has to take
care of the changing dimensions.

I want to enter following formula in cell A2:
=DATE(D1+2000,Month(C1&"1"),B1)
and copy the formula down, up to the last row in which there is a value
in column B.

Can anyone help with a piece of code, which can do that?

This is what I came up with, so far:

Selection.Copy
ActiveCell.Offset(1, 0).Range("A1:B1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:A118").Select
ActiveSheet.Paste
ActiveCell.Columns("A:A").EntireColumn.EntireColumn.AutoFit
ActiveCell.Offset(-1, 0).Range("A1").Select
Application.CutCopyMode = False

Regards,
Norbert

[Claus Busch]

Hi Norbert,

Am Mon, 9 May 2022 09:05:01 +0200 schrieb Norbert:


> I have trouble to get my macro to perform the same, even when the size
> of my table changes its dimensions.
>
> I run a report on our ERP system which gives out a txt file which I want
> to change to a different layout. Therefore I import it into Excel,
> determining the width of columns, deleting what is not needed and so on.
>
> At one stage of my macro, I have an empty column A and I want to insert
> a formula in each cell as far down as there are values in column B.
> The number of values in column B will change every time I run the query
> to get the txt file from our ERP system. Therefore my macro has to take
> care of the changing dimensions.
>
> I want to enter following formula in cell A2:
> =DATE(D1+2000,Month(C1&"1"),B1)
> and copy the formula down, up to the last row in which there is a value
> in column B.

try:

Dim LRow As Long

With ActiveSheet
LRow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Range("A2:A" & LRow).Formula = "=Date(D2+2000,Month(C2&""1""),B2)"
End With


Regards
Claus B.
--
Windows10
Microsoft 365 for business

[Norbert]

On 09-05-2022 09:37, Claus Busch wrote:
> Dim LRow As Long
>
> With ActiveSheet
> LRow = .Cells(.Rows.Count, "B").End(xlUp).Row
> .Range("A2:A" & LRow).Formula = "=Date(D2+2000,Month(C2&""1""),B2)"
> End With

Hi Claus,

thank you very much! Your code works great!
I wonder what you must be thinking, seeing my code!

Best regards,
Norbert