0

Software: Excel/VBA Goal: Use a function (located in a module) to search a range for a specific value, and paste the corresponding value with its formatting in a new location. [i.e. searching for "2" will return italicized text, see image]. Issue: I'm not sure. I'm not getting an error, and when I debug with F8, each line is read, but the values & formatting do.not.paste. Any advice would be greatly appreciated.

Things I've Tried:

  • I can get the code to work as a sub and input boxes for the inputs in the function, but when I try to make it into a function, it reads the lines, but does not actually paste the data.
  • I've used with/end with
  • I've tried static locations
  • I can pass at least the text through the function itself, but no way to get formatting with that Note: Adding an if statement later for when the value is not detected, but I'm not worried about that right now

Here's the code

Option Explicit
Function pasteWork(FndVal As Range, LookUpRng As Variant, outShift As Long)
'FndVal is the value we're looking for
'LookUpRng is the area we're searching
'outShift is the location of the output (shifting from the formula location & the shift for result)

Application.ScreenUpdating = False
Dim valFind As Range, myVal As Variant, myValLoc As Variant, newLoc As Variant

Set valFind = LookUpRng.Find(FndVal.Value, , xlValues, xlWhole)
'MsgBox "Finding this " & valFind.Offset(0, outShift).Value
myVal = valFind.Offset(0, outShift).Value
myValLoc = valFind.Offset(0, outShift).Address 'Location of the formatted text to copy

newLoc = FndVal.Offset(0, 1 + outShift).Address

    With ActiveWorkbook.Sheets("Sheet1")
        .Cells(valFind.Row, valFind.Column + outShift).Copy
        .Range(newLoc).PasteSpecial Paste:=xlPasteAllUsingSourceTheme
    End With


    
    With ActiveWorkbook.Sheets("Sheet1")
        .Cells(valFind.Row, valFind.Column + outShift).Copy
        
        .Range(newLoc).PasteSpecial Paste:=xlPasteValues

    End With

    pasteWork = valFind.Offset(0, outShift).Value 'passing the value to the function output


Application.CutCopyMode = False
Application.ScreenUpdating = True

End Function

Image of Excel Cells with Function and Source Data: Image of Excel Cells with Function and Source Data

elena.kim
  • 930
  • 4
  • 12
  • 22
Eng_SEC
  • 1
  • 3
    You can't do this with a UDF called from a cell. – BigBen May 21 '21 at 19:33
  • A UDF function can change only the cell where has been called from. – FaneDuru May 21 '21 at 19:37
  • Well there is [this workaround](https://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet) if you really want to go down that road. No guarantee it'll work in your case. – BigBen May 21 '21 at 19:49
  • https://support.microsoft.com/en-us/topic/description-of-limitations-of-custom-functions-in-excel-f2f0ce5d-8ea5-6ce7-fddc-79d36192b7a1#:~:text=A%20user%2Ddefined%20function%20called,add%20sheets%20to%20a%20workbook. – Tim Williams May 21 '21 at 19:53
  • Ah, that is definitely the information I was missing. Thanks, everyone! – Eng_SEC May 24 '21 at 11:04

0 Answers0