really strange problem with macro and formulas

RAYLWARD102

Well-known Member
Joined
May 27, 2010
Messages
527
I have a macro that imports data from a csv type file. It imports the data into a sheet named dump. After import; I have another macro that inserts a blank row after every row of data. I've done this because there are two sheets that refrence the cell values from dump and the cells in these two other sheets have all merged cells. When I tried to fill a formual down in these 2 sheets; it was automatically doing it based on odd numbers because of the merged cells; hence the reason why I had to add the blank row after every row of data in the dump sheet. After completing the formulas to work the way I wanted' I saved the workbook. IF I delete the dump data and re-start the import process; the formulas on the 2 refrence sheets change. They suddenly change to look at even numbered cells in the formulas. Does anyone no how I can stop this? I can't have the formulas changing. This is confusing! I also tried protecting the sheet. The formulas still changed!
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
Are your formulas using relative or absolute addresses?
(ie), do your cell references look like this? =A1 or like this? =$A$1

Clearly it has to do with the importing [and the merged cells :mad: ] but I would think (without knowing exactly all you're doing) absolute referencing would fix it.
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
do the formulas change right after the data import?
post the code that imports the data.
even if you use Absolute refs like HalfAce suggested - it is still possible for excel to decide to adjust the formulas.
post also the formulas - they can be made in a way to prevent this behaviour (without Cell references)
wouldn't it be better if you don't make a separate process for imserting rows - just skip each row in the import code?
post something to think on :)
 
Last edited:

RAYLWARD102

Well-known Member
Joined
May 27, 2010
Messages
527
here is my copy paste macro....

Code:
Sub open_workbooks_same_folder()
    Dim folder As String
    Dim Wb As Workbook, sFile As String
    Dim Cwb As Workbook
    Dim lrow As Long
    folder = ThisWorkbook.Path & "\"
     '    folder = "C:\Temp\"
    Set Cwb = ThisWorkbook
    sFile = Dir(folder & "*.tab")
    Do While sFile <> ""
        If sFile <> Cwb.Name Then
             'If there are sheets without a data sheet
             'continue with code to import
             'the rest that has a sheet with the name data
            On Error Resume Next
            Set Wb = Workbooks.Open(folder & sFile)
            lrow = Cwb.Worksheets("Data").Range("A" & Rows.Count).End(xlUp).Row
            'lrow = lrow + 1
            'Wb.Worksheets("july 29").Range("A17:J33").Copy
            Wb.Worksheets("july 29").Range("a2:dw300").Copy
            Cwb.Worksheets("DUMP").Range("A" & lrow + 1).PasteSpecial xlPasteValues
            Application.CutCopyMode = False
            Wb.Close True
        End If
        sFile = Dir
    Loop
    Cwb.Worksheets("DUMP").Range("A1").Select
End Sub

this is my insert row macro....and it prints out the 2 sheets that refrence the dump sheet after the rows have been inserted.

Code:
Sub Insert_Blank_Rows()
     
     'Select last row in worksheet.
    Selection.End(xlDown).Select
     
    Do Until ActiveCell.Row = 1
         'Insert blank row.
        ActiveCell.EntireRow.Insert shift:=xlDown
         'Move up one row.
        ActiveCell.Offset(-1, 0).Select
    Loop
With Sheets("MANIFEST")
    .PageSetup.PrintArea = "$A$1:$L$" & .Range("o24").Value
End With
Sheets("manifest").PrintOut Copies:=1, Collate:=True
With Sheets("INVOICE")
    .PageSetup.PrintArea = "$A$1:$M$" & .Range("r13").Value
End With
Sheets("invoice").PrintOut Copies:=1, Collate:=True
'Sheets("DUMP").Range("A1:DW300").ClearContents
End Sub

I'm not sure what you mean about my formulas using relative or absolute. All I know is that when I set my formulas up on the refrence sheets; and fill down......It works until I import new data into the dump.
 

RAYLWARD102

Well-known Member
Joined
May 27, 2010
Messages
527

ADVERTISEMENT

is there a way for me to lock the formulas permantley on the 2 sheets that refrence my dump data?
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
is there a way for me to lock the formulas permantley on the 2 sheets that refrence my dump data?

Not that i know of
but there is a way to make them independent of the cell references as such-
what i mean is to use functions as Indirect, Offset, Row, Col etc. and not ot refer directly to the cell address
 

RAYLWARD102

Well-known Member
Joined
May 27, 2010
Messages
527

ADVERTISEMENT

My formulas are relative according to a description I just looked at. How can I make them absolute without having to go and write them all manually?
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
i will not get involved with the code right now but what i think is very likely to be happening:
importing the data is no problem.
but then i think the inserting of rows - IS the real one.
first check - are the refences changing really only ot even rows or there is increasing relocation (i don't know how to call it)
for example:
you have a ref. ot cell B3 but then you enter a row between 2 and 3 so Excel adjusts it to B4 and the one in B5 is moved to B6 (b4 -> B5 probably will stay unrefernced) ....
anyway it is too complicated for me to explain this but finally the result is a mess.
Probably only the first reference is intact.
so solution one shoul be: build better and reference independent formulas.
solution two: do not insert rows in this way

example for solution one:
=INDIRECT("Sheet1!"&"B"&47,1)
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
My formulas are relative according to a description I just looked at. How can I make them absolute without having to go and write them all manually?
no matter if you make the relative or absolute - when you insert rows Excel will automatically adjust/change the refernces. so sheck my previous post.
the main idea is you have to replace the references with formulas which will not be adjusted
the other way - avoid inserting rows
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,450
When you insert new rows, any formulas that referenced cells that were shifted by the row-inserts will change to reference the the same cells that have been shifted to a new row. In other words, inserting rows is what's causing your formulas to change the cells they reference.

One way around that is to "copy" your data to every other row and not insert new rows e.g.
Code:
    'Select last row in worksheet.
    Selection.End(xlDown).Select
     
    Do Until ActiveCell.Row = 1
        ' Copy to every other row
        Rows(ActiveCell.Row * 2 - 1).Value = ActiveCell.EntireRow.Value
        ActiveCell.EntireRow.Clear
        'Move up one row.
        ActiveCell.Offset(-1, 0).Select
    Loop

This should leave your formulas unchanged.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,302
Messages
5,836,516
Members
430,437
Latest member
Emilycr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top