Macro formula for counting rows

[Bud]

Hello

We are copying data from a worksheet in workbook#1 into a workbook#2 with a
macro in it and into Worksheet A specifically of that workbook #2.

While in a Macro fo workbook #2...............
I need to figure out how many rows of data there is in worksheet A. This can
vary from week to week.
I than want to use that number to start in worksheet B and do a fill down
starting in Row 2 of worksheet B and fill down columns A, B, C, D, E, and F
for that many rows. Row 2 of worksheet B has formulas that I need to fill
down with. Those formulas are in column A,B,C,D,E,F of row2.

Also, I need to identify the #NA and for specific task names I need to
change field E and F to specific values. Maybe I can do that with another
macro inside the workbook

Thanks

 

[Barb Reinhardt]

Use this to counting rows in column 1 on aWS

lRow = aWS.Cells(aWS.rows.count,1).end(xlup).row

 

[Bud]

Do you also have code for these 2 other conditions I listed in the previous
note...Sorry I didn't make that very clear....maybe this is to much to ask
here....

Thanks!

I have formulas in worksheet B in row2 across columns A,B,C,D,E, and F.
I want to use the fill down feature for a number of rows which can be
different each time I use this. Can you provide some simple code for this?

I also need to identify any #NA's in column D and for specific values in
column C change those to use the first 3 characters of column C....add 5
zeros...and use the next 3 characters of column C thus changing the value in
column D. Can you provide some simple code for this?

 

[Barb Reinhardt]

As far as the NA question goes, you can change your formula in COlumn D to
something like this.

IF(ISNA("put your formula for D1 in here"), LEFT(C1,3) & "00000" &
MID(C1,3,3),"D1 Formula again)

You're going to have to explain the other part in more detail. I'm not sure
what you're driving at.

--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.

 

[Bud]

sure....

What we do today is have someone count the rows in a worksheet titled
SAP_Import. This is a worksheet containing timesheet data including task
description. It has no client data fields in it.
In another worksheet called EnhSave is a list of client data including task
description. This has no timesheet data in it.
In another worksheet called Fill-Down we have formulas in row2 across
columns A,B,C,D,E, and F.
After obtaining the number of rows in worksheet SAP_Import(Timesheet data)
we manually scroll down that number of rows in the Fill-Down worksheet. That
worksheet than has formulas to match on data and provide a client report of
timesheet data for the week.

We do this because the SAP system from which we import the data does not
have the client fields in it.

We want to automate that so it can auto populate the Fill-Down worksheet.

In columnA row 2 we have =SAP_Import!A2, In columnB row2 we have
=SAP_Import!B2, In Column E we have
=IF($D$2:$D$2500="","",(INDEX(EnhSave!$F$1:$F$2500,MATCH($D$2:$D$2500,EnhSave!$B$1:$B$2500,0))))

Can you provide some simple code for this?