really strange problem with macro and formulas

RAYLWARD102

Well-known Member
Joined
May 27, 2010
Messages
521
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!
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
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,343
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
521
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
521

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,343
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
521

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,343
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,343
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,276
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,719
Messages
5,524,459
Members
409,581
Latest member
khin

This Week's Hot Topics

Top