really strange problem with macro and formulas

RAYLWARD102

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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
is there a way for me to lock the formulas permantley on the 2 sheets that refrence my dump data?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

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
Back
Top