merging two related postings
Want to take my excel formula and use it in VBA?
-
igotmailyay9 -
March 20, 2019 at 9:19 PM -
Thread is marked as Resolved.
-
-
-
Hi,
The quick shortcut :
Turn on your macro recorder ...
and input your formula ... :wink:
Hope this will help
-
Thanks for the quick tip. So far I haven't have any issues. Would you have any suggestions on how I can take this formula and apply it to about 1500 cells throughout my range? Some of the criteria within the formula changes along with each cell but it is repeatable depending on what row or column the cell is in. Appreciate for feedback greatly!
-
Hi again,
To make things easier ... why don't you post a sample file with your next message ... :smile:
-
Post the VBA code that you produced using the recorder so that it can be amended.
-
-
Thanks for the feedback. I am new to the forum and don't really know my way around very well.
Here is a sample. In "Report" sheet I want cell B3 to look at all the other datasheets in workbook and add up all the "miles" a truck has driven in that state. Sometimes a truck drives in a state and sometimes they don't so that would produce a 0 value for that truck in that state. There are 7 datasheets that represent the 7 days of the week. The "Report" sheet is calculating what truck it is, what state it drove in, and tallies all the miles for the week, sheet by sheet in the "Report" sheet cell that corresponds. Below what I have so far to prove my formula theory in the "Reports" tab and the other is a representation of all the "datasheet" tabs that are created. I apologize if this is long.
**Report Sheet / the info will continue to expand to making a final row of 10000 makes sense
**Datasheet / the values change from sheet to sheet but the format stays consistent / 1. what truck (column B). 2. what state (column A). 3 Capture the miles in column E if there are miles to capture. 4. Be able to reproduce this formula from range (A3,Z10000) on the Report sheet.
-
This is the datasheet that corresponds to the previous posts "report" sheet photo.
-
I want to take the formula in the Report photo and make it a VBA formula that will capture cells in a range from (A3,Z10000) to account for any adjustments in the future. In the Report photo my variables are truck and state. I need to find, in every datasheet1 through datasheet7 (for 7 days in a week) all trucks that have driven in a particular state. If they did I need to cumulatively calculate that value and put into the corresponding cell in the report.
-
=IFERROR(INDEX(datasheet1!E3:E301,MATCH(1,(datasheet1!A3:A301=B2)*(datasheet1!B3:B301=A3),0)),"0")+IFERROR(INDEX(datasheet2!E3:E301,MATCH(1,(datasheet2!A3:A301=B2)*(datasheet2!B3:B301=Report!A3),0)),"0")+IFERROR(INDEX(datasheet3!E3:E301,MATCH(1,(datasheet3!A3:A301=B2)*(datasheet3!B3:B301=Report!A3),0)),"0")+IFERROR(INDEX(datasheet4!E3:E301,MATCH(1,(datasheet4!A3:A301=B2)*(datasheet4!B3:B301=Report!A3),0)),"0")+IFERROR(INDEX(datasheet5!E3:E301,MATCH(1,(datasheet5!A3:A301=B2)*(datasheet5!B3:B301=Report!A3),0)),"0")+IFERROR(INDEX(datasheet6!E3:E301,MATCH(1,(datasheet6!A3:A301=B2)*(datasheet6!B3:B301=Report!A3),0)),"0")+IFERROR(INDEX(datasheet7!E3:E301,MATCH(1,(datasheet7!A3:A301=B2)*(datasheet7!B3:B149=Report!A3),0)),"0")
this is the formula written in my report cell if its hard to read.
-
For sure you know photos are inert objects ...
As already indicated ... you should attach a sample file ... :smile:
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!