Home 
Search 
Today's Posts 
#1




Cell to be activated, which is a result of a vlookup formula
Hi,
could somebody please help me with some code for a macro? I have following formula: =VLOOKUP($H6&""&I6,'Stock sheet'!$A:$F,6,FALSE) After the result is displayed, I'd like the cursor to jump to that exact cell, which it found in 'Stock sheet'!$A:$F,6,False). I tried Goto, but it doesn't work. Thank you! Regards, Norbert 
#2




Cell to be activated, which is a result of a vlookup formula
Hi,
could somebody please help me with some code for a macro? I have following formula: =VLOOKUP($H6&""&I6,'Stock sheet'!$A:$F,6,FALSE) After the result is displayed, I'd like the cursor to jump to that exact cell, which it found in 'Stock sheet'!$A:$F,6,False). I tried Goto, but it doesn't work. Thank you! Regards, Norbert GoTo only requires the target address;  ",False" belongs to the VLOOKUP function!  Garry Free usenet access at http://www.eternalseptember.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion 
#3




Cell to be activated, which is a result of a vlookup formula
Hi Norbert,
Am Sun, 2 Feb 2020 08:31:29 0800 (PST) schrieb Norbert: I have following formula: =VLOOKUP($H6&""&I6,'Stock sheet'!$A:$F,6,FALSE) After the result is displayed, I'd like the cursor to jump to that exact cell, which it found in 'Stock sheet'!$A:$F,6,False). I tried Goto, but it doesn't work. try: Sub Test() Dim c As Range Dim myStr As String With ActiveSheet myStr = .Range("H6") & "" & .Range("I6") End With With Sheets("Stock sheet") Set c = .Range("A:A").Find(myStr) Application.Goto .Range("F" & c.Row) End With End Sub Regards Claus B.  Windows10 Office 2016 
#4




Cell to be activated, which is a result of a vlookup formula
Hi Claus,
thanks for your reply! I am trying to incorporate your code into my macro but it stops at line: Application.Goto .Range("F" & c.Row) with following message: Runtime error 91: Object variable or With block variable not set! Herewith my macro and how I tried to make use of your code: Sub FEED_STOCK_REQUIREMENTS() Range("I6:L6").Select Selection.ClearContents Range("F6").Select Selection.Copy Range("I6").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Selection.TextToColumns Destination:=Range("I6"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _ :="+", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True Range("L6").Select ActiveCell.FormulaR1C1 = "=RC[5]/COUNTA(RC[3]:RC[1])" Range("B6").Select ' Goto the cell which was found by the vlookup formula in ' Sheet("Input").Range(I8) Dim c As Range Dim myStr As String With Sheets("Input") myStr = .Range("H6") & "" & .Range("I6") End With With Sheets("Stock sheet") Set c = .Range("A:A").Find(myStr) Application.Goto .Range("F" & c.Row) End With End Sub 
#5




Cell to be activated, which is a result of a vlookup formula
On Tuesday, 4 February 2020 07:54:47 UTC+2, Norbert wrote:
Hi Claus, thanks for your reply! I am trying to incorporate your code into my macro but it stops at line: Application.Goto .Range("F" & c.Row) with following message: Runtime error 91: Object variable or With block variable not set! Herewith my macro and how I tried to make use of your code: Sub FEED_STOCK_REQUIREMENTS() Range("I6:L6").Select Selection.ClearContents Range("F6").Select Selection.Copy Range("I6").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Selection.TextToColumns Destination:=Range("I6"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _ :="+", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True Range("L6").Select ActiveCell.FormulaR1C1 = "=RC[5]/COUNTA(RC[3]:RC[1])" Range("B6").Select ' Goto the cell which was found by the vlookup formula in ' Sheet("Input").Range(I8) Dim c As Range Dim myStr As String With Sheets("Input") myStr = .Range("H6") & "" & .Range("I6") End With With Sheets("Stock sheet") Set c = .Range("A:A").Find(myStr) Application.Goto .Range("F" & c.Row) End With End Sub Hi Claus, it works, the problem was that I actually had formulas in Range (A:A) on sheet "Stock sheet" and not values. The formulas created the string I was searching with the vlookup formula. This is one of the formulas: =C5&""&D5 It would be great if it was working with the formulas, otherwise I have to come up with another idea. 
#6




Cell to be activated, which is a result of a vlookup formula
Error is because Find() failed to Set c
try... With Sheets("Stock sheet") Set c = .Range("A:A").Find(myStr) If Not c Is Nothing Then Application.Goto .Range("F" & c.Row) End With  Garry Free usenet access at http://www.eternalseptember.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
VLookup  result (cell) shows formula, not lookup data  Excel Discussion (Misc queries)  
vlookup shows result one cell above the expected result  Excel Worksheet Functions  
Vlookup is displaying the formula in its cell not the result??  Excel Worksheet Functions  
Advanced formula  Return result & Show Cell Reference of result  Excel Worksheet Functions  
VLOOKUP formula appears in the cell I need to see the result in  Excel Discussion (Misc queries) 