Range in formula changes when running a macro

[Morgan Moore]

Hi all!

I am new to this forum...my first time in any type of excel forum, so I ask that you please forgive my ignorance! I am here to learn. I have moderate skills in excel and VBA, but self taught. This may be too much information, but here is my background/question:

-I have created a workbook to generate reports of certain data.

-"Sample Info", "List Form", and "Report Template" are the tabs I am working with.

-"Sample Info" contains a list of sample numbers and product names entered by a UserForm.

-"List Form" is a list of data copied from another tab and cleaned up so that I can use INDEX and MATCH

-"Report Template" is what it sounds like. It has formulas built in the template to reference "Sample Info" (to pull product name and sample number to the report), and "List Form" to find data from the list based on two criteria, and paste it into the report.

-The tricky part is that I wrote a macro to find the sample numbers in the "Sample Info" tab, and use the "Report Template" to generate a report for each sample.

-THE PROBLEM...every time a report is generated, the formula range is adjusted automatically, and therefore some data is missed with a new generation. Here the formula within "Report Template":

{=INDEX('List Form'!$V$2:$V$500,MATCH(1,IF('List Form'!$Q$2:$Q$500=--$I$2,IF('List Form'!$B$2:$B$500=$D8,1)),0))}

I run the macro and it changes to:

{=INDEX('List Form'!$V$1:$V$422,MATCH(1,IF('List Form'!$Q$1:$Q$422=--$I$2,IF('List Form'!$B$1:$B$422=$D8,1)),0))}

And again:

{=INDEX('List Form'!$V$1:$V$352,MATCH(1,IF('List Form'!$Q$1:$Q$352=--$I$2,IF('List Form'!$B$1:$B$352=$D8,1)),0))}

Here is the code to generate new tabs based on list of sample numbers:

Sub NewTab()

'This finds the sample numbers from the sample info tab and creates new tabs with the report format and renames the tabs as the sample number

hh = Sheets("SampleInfo").Cells(Rows.Count, 1).End(xlUp).Row - 1
Sheets("Report Template").Select
Application.DisplayAlerts = False
nn = 0
Do Until nn = hh
Sheets("Report Template").Select
Sheets("Report Template").Copy After:=Sheets(6 + nn)
ActiveSheet.Name = Sheets("SampleInfo").Range("C1").Cells(2 + nn, 1).Value
nn = nn + 1
Loop
End Sub

***I need the formula range in to stay constant at 2:500 (the formula that is in green text) every time I run the macro. This is in the case that someone notices a mistake and needs to regenerate the report.

Thank you for your help in advance! I did not want to upload the spreadsheet yet, unless this is a simple fix, which I am hoping is the case.

Morgan

 

[Luke M]

Welcome to the forum Morgan. You did an excellent write-up, describing the layout, problem, and what you would like to see.

Looking at your macro, there's nothing in it that should be messing with your formulas. I was expecting to see something adding/deleting rows of cells, which would cause the formulas to change. Is there perhaps another macro that is being run, or something else that is moving either the formulas, or the cells they are referencing?

Not an answer to what's going on, but another idea might be to use XL Table structures. Not sure what whole data layout looks like, but if you put your info in a table, you can reference a column of information like:
=SUM(Table1[My Column])
Then you don't have to worry about adjusting cell references. Am still curious as to how your formulas are changing...

 

[Morgan Moore]

Thanks Luke! There is actually another macro ran prior to this one...It's a dirty macro that I just recorded to delete spaces and hide other columns...I need to clean it up. This must be where the problem is?

I did this because I couldn't get the INDEX and MATCH formula previously mentioned to work without making it a list...or in a table form. The raw data contains spaces and headings. Do you know another way around that? I would prefer to use the raw data to search for the data value.

Unless I don't understand, I don't think I can use =SUM( because i need two criteria to be met before returning a value. I need a sample number to match the one on the report, and also the analyte to match, so that it returns the correct concentration. Here is what it generally looks like:



Here is the deleting macro:

Sub ListForm()
'
'ListForm Macro
'Copies data from the calculations tab, pastes the values only, hides un-needed columns, and deletes all spacing. This allows the index/match formulas in the report template to search the data.
'Morgan Moore July 2014

  Sheets("Calculations").Select
  Cells.Select
  Selection.Copy
  Sheets("List Form").Select
  Cells.Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  :=False, Transpose:=False
  Rows("1:6").Select
  Application.CutCopyMode = False
  Selection.Delete Shift:=xlUp
  Rows("1:1").Select
  Selection.Font.Bold = True
  Rows("44:51").Select
  Selection.Delete Shift:=xlUp
  ActiveWindow.SmallScroll Down:=66
  Rows("86:93").Select
  Selection.Delete Shift:=xlUp
  ActiveWindow.SmallScroll Down:=45
  Rows("128:135").Select
  Selection.Delete Shift:=xlUp
  ActiveWindow.SmallScroll Down:=42
  Rows("170:177").Select
  Selection.Delete Shift:=xlUp
  ActiveWindow.SmallScroll Down:=42
  Rows("212:219").Select
  Selection.Delete Shift:=xlUp
  ActiveWindow.SmallScroll Down:=42
  Rows("254:261").Select
  Selection.Delete Shift:=xlUp
  ActiveWindow.SmallScroll Down:=42
  Rows("296:303").Select
  Selection.Delete Shift:=xlUp
  ActiveWindow.SmallScroll Down:=42
  Rows("338:345").Select
  Selection.Delete Shift:=xlUp
  ActiveWindow.SmallScroll Down:=42
  Rows("380:387").Select
  Selection.Delete Shift:=xlUp
  
  Columns("C:P").Select
  Selection.EntireColumn.Hidden = True
  Columns("R:U").Select
  Selection.EntireColumn.Hidden = True
  Columns("A:Z").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Columns("A:Z").EntireColumn.AutoFit
  Columns("C:P").Select
  Selection.EntireColumn.Hidden = True
  Columns("R:U").Select
  Selection.EntireColumn.Hidden = True
  Columns("AA:AD").Select
  Selection.EntireColumn.Hidden = True
  Columns("B:B").Select
  With Selection
  .HorizontalAlignment = xlLeft
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Range("A1").Select
  ActiveWorkbook.Save
End Sub

 

[Luke M]

Could the SUMIFS function help you out? Let's you give multiple criteria ranges.

From the calculation table layout, your current formulas look like they would work, regardless of the spaces. But you say you were getting an error?

 

[Morgan Moore]

LUKE! I went back and tried my formula with the raw data tab and it worked beautifully. I'm not exactly sure what I may have done differently, but I'm just going to eliminate the "List Form" section with that part of the macro all together. Thanks for your confidence and help.

 

[Luke M]

Glad I could be of help. Hope to hear from you again sometime.