macro to work for variable number of rows

Hi guys I need some help with macros for variable data. In the attached file, the sheet named Front Page is the report I would like.

The input page is where the data would be pasted into. I have created sample sheets named Sample 1 and Sample 2 which have the format of the data that will be uploaded.

I would be copying and pasting the data into the input page and would like the macro to find the items from the Front Page in the Input data and paste as values only in the relevant columns.

Example:

The refresh button is used to clear all values in the cells. The Open P&L Input Sheet opens the relevant worksheet and clears everything in the worksheet. I would then paste the raw data into the Input page and run the macro “Feed Data”

Basically, the Feed Data macro will do the following:

1. Open Front Page worksheet and go to the Total Net Interest Income area
2. Go to Input Page work sheet and find “Total Net Interest Income” and copy values in cells A41, B41 and C41 (for this sample data) and paste values only to cells B8, C8 and D8 in the Front Page worksheet.
3. Go back to Input Page and copy values in cells F41, G41 and H41 and copy values only to cells H8, I8 and J8 in the Front Page worksheet.
4. Repeat same process for the rest of the items.

I tried creating the macro but the only problem is that if the number of rows is different (like the 2 samples), the macros won’t work.

I created the macro using Sample Data 1 but if Sample data 2 is used, it comes up with errors.

I would really appreciate if someone could help me out.

Thanks heaps.

Ps: Do not worry about the Grey Areas.

Hi

Didn't test this too hard but try

Please Login or Register  to view this content.

You will have to change the heading in A30 to TOTAL DEPRECIATION (EXCL OPERATING LEASE) to exactly match the heading in the data.


hth

rylo

hi there! thanx for your reply but that code still returns errors mate!

HI

Did a bit more testing and the sample 1 data comes back the same.

Can't be sure about the sample 2 data as you don't have numbers for that.

Where is it giving errors, and what are the correct answers.


rylo

thanks for your reply Rylo.

I want to be able to work around that, so if there is no "Net Interest Income" in a set of sample data, I do not want it to return and error instead, look for the next item.

Also with that code, when I use sample data 2, its looking for specific cell references and the problem is different data samples with have different number of rows.

Thanks

Originally Posted by rylo

HI

Did a bit more testing and the sample 1 data comes back the same.

Can't be sure about the sample 2 data as you don't have numbers for that.

Where is it giving errors, and what are the correct answers.


rylo

Just a quick follow up. The code doesn't return the same data even for Sample 1. If you delete rows 11 to 31 (using Sample Data 1) and run the code, it will give you different numbers altogether. I would like the macro to work for variable rows ... so no matter what row the item is in, it will still pick it up from there.

Cheers!

Anyone able to help me with this one?

Originally Posted by rylo

HI

Did a bit more testing and the sample 1 data comes back the same.

Can't be sure about the sample 2 data as you don't have numbers for that.

Where is it giving errors, and what are the correct answers.


rylo

Mate your're a genius. That code works brilliantly. Just a minor problem, the code looks for the first instace of the item so for example if we're looking for TOTAL PERSONNEL, it will pick the first instance of that occurance as there are a few occurances.

I have attached Sample Data 3 so you can see what I am talking about. When you run the code, it is returning the value in cell A220 of the sample data instead of A317.

Can i get a code to match the items exactly (like in my previous macro using the macro recorder)?

Thanks heaps once again. You're a champ!!!

Hi

Change the row

Set findit = .Range("E:E").Find(what:=outsh.Cells(arr(i), 1).Value)

to

Set findit = .Range("E:E").Find(what:=outsh.Cells(arr(i), 1).Value, lookat:=xlWhole)


rylo

Originally Posted by rylo

Hi

Change the row

Set findit = .Range("E:E").Find(what:=outsh.Cells(arr(i), 1).Value)

to

Set findit = .Range("E:E").Find(what:=outsh.Cells(arr(i), 1).Value, lookat:=xlWhole)


rylo

You're the man!!!

Thanks heaps for all your help mate!

Cheers