VBA Loop Range and Insert Values in New Column

[PolleschFiles]

I'm brand new to the forum. Normally, I can check the sites and find what I am looking for and haven't had to register... until now.
Some background to my problem:

I built a Microsoft Access database for my church to track member information. I was able to import a lot of the needed information to create the records for each individual and household easily. What I can't do is import their offering donations until it is "Normalized." Which means that I need each row to be 1 record with the fields listed as the column headings. My church has a spreadsheet with several sheets one of which is named Input. I've attached the desensitized file for you to view/play with.

What I need to do is loop through the input sheet and find all the values in range B:4 to GN:387 that are greater than zero or not null (I believe they entered the default as zero so their other calculations would work). Then I need those values to be transferred to the column labeled Offering Amount on the ImporttoAccess sheet. Somehow I also need to define three variables associated with the cell that is contains a value greater than zero.
1. I need the row heading value for the envelope number (Column A of input sheet) which intersects with the amount
2. I need the column heading value (3 field merged) for the date (Row 1 of input sheet) which intersects with the amount
3. I need the column heading value (labeled: GenFund, WLA, or Missions) for the designation of the offering (Row 2 of input sheet) which intersects with the amount

Each of these I need to attach to the value offering amount and insert into their corresponding column fields for that record. I have entered 2 example rows of data into the ImporttoAccess sheet for you to see what I am after. I am sure it's possible since the data is there, But I have no idea how to go about it.

Thanks.

[JBeaucaire]

I don't see the sample output on the ImportToAccess sheet

[PolleschFiles]

Huh, don't I look dumb. I attached the file while writing the thread and then thought I should give a sample of my data needed. So, I thought I had removed the original attachment and reattached the new doc with the sample data. Apparently not. At any rate. I've reattached the document and also highlighted the field values in the input sheet that I sampled in the ImporttoAccess sheet.

[JBeaucaire]

This should do it. It takes a minute or so to run on all that data... it would run faster if you didn't have all those zeros in the empty cells, but it is what it is.

Please Login or Register  to view this content.

[PolleschFiles]

Thank you so much! I copied your code and pasted into a command button and it works exactly the way I need it. I know how to manipulate controls and fields in Access with VBA but Excel is a bit different when referring to cells and ranges and such. So I had no idea where to start. (I am a landscaper by profession hoping to get into database administration and have been volunteering my time to the church to create this for them.) Thank you so much again.

God Bless