Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA insert messing up Vlookup and Match
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 --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA insert messing up Vlookup and Match
Sau,
It sounds like you're overwriting some stuff, but you need to be more specific. Rob "Sauron " wrote in message ... 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 --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA insert messing up Vlookup and Match
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 -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA insert messing up Vlookup and Match
Is there a way to 'lock' the vlookup formulas does anyone know?
Cheers, Sau --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA insert messing up Vlookup and Match
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA insert messing up Vlookup and Match
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. "Sauron <" wrote: Any thoughts Rob? :( --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA insert messing up Vlookup and Match
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. "Colo <" wrote: 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. --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA insert messing up Vlookup and Match
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 "Sauron " wrote in message ... Any thoughts Rob? :( --- Message posted from http://www.ExcelForum.com/ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA insert messing up Vlookup and Match
Cool, cheers for the responses!
I don't mean to ignore you. I only get a couple of hours each night t answer questions. 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(O1Q1,"",IF(O1<=-T2-,S2,IF(Q1S2,MATCH(O1,-T2:T1000-,1),""))))) =IF(T3="","",IF(N2=$R$1,"",IF(N2=$Q$2,"",N2+1))) Becomes; =IF(#REF!="","",IF(Q2="","",IF(O1Q1,"",IF(O1<=#RE F!,S2,IF(Q1S2,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 -- Message posted from http://www.ExcelForum.com |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA insert messing up Vlookup and Match
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 " wrote in message ... Cool, cheers for the responses! I don't mean to ignore you. I only get a couple of hours each night to answer questions. 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; =IF(-T2-="","",IF(Q2="","",IF(O1Q1,"",IF(O1<=-T2-,S2,IF(Q1S2,MATCH(O1,-T2: T1000-,1),""))))) =IF(T3="","",IF(N2=$R$1,"",IF(N2=$Q$2,"",N2+1))) Becomes; =IF(#REF!="","",IF(Q2="","",IF(O1Q1,"",IF(O1<=#RE F!,S2,IF(Q1S2,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! it 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 its formula as well. Thanks for all the replies all, appreciated. Cheers, Sau --- Message posted from http://www.ExcelForum.com/ |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA insert messing up Vlookup and Match
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 -- Message posted from http://www.ExcelForum.com |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA insert messing up Vlookup and Match
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.) "Sauron <" wrote: Cool, cheers for the responses! I don't mean to ignore you. I only get a couple of hours each night to answer questions. 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; =IF(-T2-="","",IF(Q2="","",IF(O1Q1,"",IF(O1<=-T2-,S2,IF(Q1S2,MATCH(O1,-T2:T1000-,1),""))))) =IF(T3="","",IF(N2=$R$1,"",IF(N2=$Q$2,"",N2+1))) Becomes; =IF(#REF!="","",IF(Q2="","",IF(O1Q1,"",IF(O1<=#RE F!,S2,IF(Q1S2,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! it 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 its formula as well. Thanks for all the replies all, appreciated. Cheers, Sau --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA insert messing up Vlookup and Match
Excellent, thanks! the indirects worked perfectly!
Cheers! Sau --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup retunrning a match, when not a match... | Excel Worksheet Functions | |||
vlookup retunrning a match, when not a match... | Excel Worksheet Functions | |||
vlookup retunrning a match, when not a match... | Excel Worksheet Functions | |||
index Match, or Vlookup Match.. | Excel Worksheet Functions | |||
Tab character is messing me up | Excel Discussion (Misc queries) |