Excel VBA Function for Value AND Format Lookup
Sub test()
On Error GoTo Suberror
'turn off screen updating
Application.ScreenUpdating = False
'ask for input
strName = InputBox(Prompt:="Lookup Value", _
Title:="Lookup Value", Default:="0")
'if no input - exit
If strName = "0" Or _
strName = vbNullString Then
Exit Sub
'otherwise Find
Else
Columns("A:A").Select
Selection.Find(What:=strName, After:=ActiveCell, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False).Activate
'Copy
ActiveCell.Offset(0, 1).Copy
End If
'Paste to the range that you define
Range("J1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Suberror:
Range("A1").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Related videos on Youtube
BigBrother
Updated on September 18, 2022Comments
-
BigBrother over 1 year
I need a custom function to lookup both a cell value and copy the source format. I have a list of values in two columns. The first is of integers and the second column is the corresponding text values. For example:
A B
--- ----------
1 My first value
2 My second value
3 My third valueI want to lookup the value I provide as it corresponds to those in column A. Based on the row number of the cell found with the matching value in column A, it will select the corresponding value in column B. This is easy enough with the LOOKUP() function in Excel. However, I want to ALSO copy the text formatting of the cell in column B.
Initially I approached this using a VBA function but unfortunately a function cannot change the formatting of a cell. Would appreciate ideas on how to approach.
-
Raystafarian about 12 yearsYou want to lookup a string in column A and return the value and formatting of the corresponding value in column B? Return it to where? And where is the value that you're looking up?
-
-
BigBrother about 12 yearsThanks for providing this Raystafarian! The intention was to use this as a function such as =LookupWithFormatting(A1,IDSource,TextSource). The parameters in this example are identical to the MSExcel =LOOKUP() function. That's how I was hoping to approach this. Thoughts?
-
Raystafarian about 12 years@BigBrother lookup will only return a value, not formatting
-
BigBrother about 12 yearsYep - therein is what started this whole pain point. I started a solution by writing a custom VBA function that accepted the three parameters as =LOOKUP(...) does. In the VBA function code I call LOOKUP(...) AND I had hoped to use code to copy formatting as well but it didn't work. I discovered MS Excel VBA functions cannot modify formatting. Any ideas?
-
Raystafarian about 12 yearsMy idea was the VBA code above. It will take your input, look it up and copy the other value (and formatting) to your defined range. I read it as VBA solution, not a worksheet function solution
-
BigBrother about 12 yearsThanks for taking the shot! Do you think its not possible to do this as a function?
-
Raystafarian about 12 yearsI didn't think functions could return anything except values, but I could be wrong, though I can't find any documentation to the contrary.