VBA insert messing up Vlookup and Match

  • Thread starter Thread starter Sauron
  • Start date Start date
S

Sauron

Hi All,

Just wondering if anyone had run into the problem of when you update a
column with VBA then all your VLookup and Match references pointing at
it become #REF.

Is there a way to stop this happening because its kind of annoying :/

Cheers again,
Sau
 
Sau,

It sounds like you're overwriting some stuff, but you need to be more
specific.

Rob
 
I'm copy-pasting over a table of info but the vlookups referring to th
area I'm pasting to don't like it and change their formulas to #Ref.

Cheers Guys,
Sa
 
I'm not Rob, but are you sure you're copying--not cutting, then pasting?

You may want to post that part of your code if this didn't help.
 
Hello Sauron,

Not sure, but please try this if the pasted values are treated as
STRING in the vlookups referring to the area.

1. Clear the vlookups referring to the area.
2. Copy data
3. Paste values to the vlookups referring to the area with PasteSpecial
Paste:=xlPasteValues, Operation:=xlAdd,

Anyway we need more information including your code.
 
I don't think I've gotten Ref errors with this. (Well, not that I can recall
anyway???).

I usually get Ref's when I ask to bring back a column not included in the lookup
range --like: =vlookup(a1,sheet2!a1:c9,56,false)

or when I actually cut the range out.
 
Sauron,

I don't mean to ignore you. I only get a couple of hours each night to
answer questions.

I don't think locking the vlookup is the right option. Better to fix the
root cause.

I've done a few tests here and can't replicate your problem of #REF when
strictly overwriting.
The problem can occur if the column_offset_num is greater than the number of
columns in the table array.

Without looking at the code, I can't tell for sure what the problem is.

Rob
 
Cool, cheers for the responses!
I don't mean to ignore you. I only get a couple of hours each night t
answer questions.
Click to expand...

Noo! I wasn't being impatient, I was just a little sad at my ow
efforts when I still couldn't get it working, sorry! it wasn't directe
at you! I know there's alot of posters I'm glad of any reply...

Code I'm running;

Sub RemEmptyFinal()

Application.ScreenUpdating = False
Dim rng As Range, ix As Long

'Sheet1

Application.Calculation = xlCalculationManual
Sheets("AOwens").Select
Sheet1.Range("W2:W1000").Select
Selection.Copy
Sheet1.Range("U2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
True, Transpose:=False
Set rng = Intersect(Selection, ActiveSheet.UsedRange)
If rng Is Nothing Then
GoTo done
End If
For ix = rng.Count To 1 Step -1
If Len(Trim(Replace(rng.Item(ix).Formula, Chr(160), ""))) _
= 0 Then rng.Item(ix).Delete (xlUp)

Next

~~~~~~~~~~~

This;

=IF(-T2-="","",IF(Q2="","",IF(O1>Q1,"",IF(O1<=-T2-,S2,IF(Q1>S2,MATCH(O1,-T2:T1000-,1),"")))))

=IF(T3="","",IF(N2>=$R$1,"",IF(N2>=$Q$2,"",N2+1)))

Becomes;

=IF(#REF!="","",IF(Q2="","",IF(O1>Q1,"",IF(O1<=#REF!,S2,IF(Q1>S2,MATCH(O1,T2:T472,1),"")))))

=IF(#REF!="","",IF(N2>=#REF!,"",IF(N2>=$Q$2,"",N2+1)))

Any formula directly referencing the replaced range becomes #REF! i
seems to be at random so could it be because the cell is in transition
I don't know I'm confused...

If I just have the Vlookup or Match statement it does it just to it
formula as well.

Thanks for all the replies all, appreciated.

Cheers,
Sa
 
That code runs OK and doesn't destroy the formula on my computer.

Could you step through each line (use the VBA debugger, press F8 to step
through the lines)
Make sure the screenupdating is turned on and you can see when and which
line corrupts your formula.

Rob


Sauron > said:
Cool, cheers for the responses!


Noo! I wasn't being impatient, I was just a little sad at my own
efforts when I still couldn't get it working, sorry! it wasn't directed
at you! I know there's alot of posters I'm glad of any reply...

Code I'm running;

Sub RemEmptyFinal()

Application.ScreenUpdating = False
Dim rng As Range, ix As Long

'Sheet1

Application.Calculation = xlCalculationManual
Sheets("AOwens").Select
Sheet1.Range("W2:W1000").Select
Selection.Copy
Sheet1.Range("U2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
True, Transpose:=False
Set rng = Intersect(Selection, ActiveSheet.UsedRange)
If rng Is Nothing Then
GoTo done
End If
For ix = rng.Count To 1 Step -1
If Len(Trim(Replace(rng.Item(ix).Formula, Chr(160), ""))) _
= 0 Then rng.Item(ix).Delete (xlUp)

Next

~~~~~~~~~~~

This;
Click to expand...
 
Cheers Rob, I stepped through the code and it occurs when the cell i
deleted then for that split second the cell doesen't actually exist an
so it has to change to #REF!... I should have realised it would b
that.

Nightmare, have to try and think of a way round.

Cheers,
Sa
 
If you always want a formula to point at T2 (say), and sometimes you're going to
delete T2, you could wrap it in =indirect().

=if(indirect("t2")="",.....

(I didn't understand what those -T2- meant in your formula, though.)

Another option.

change all your formulas to text, do your processing, then change them back to
formulas.

Option Explicit
Sub testme()
With Worksheets("sheet2")
.Cells.Replace what:="=", replacement:="$$$$$=", lookat:=xlPart
End With

'do your clean up code.

With Worksheets("sheet2")
.Cells.Replace what:="$$$$$=", replacement:="=", lookat:=xlPart
End With
End Sub

(This method won't work if you really wanted formulas to adjust, though.)
 
Back
Top