CODEWITHSUNDEEP
vbaexcel
RBC Kyle Bullard
RBC Kyle Bullard

Reputation: 87

IF Statement to Fill a Cell based on a Reference

This is a very weird request:

I would like a VBA macro or an IF statement to read where a reference is pulled from and populate another cell showing where the cell was referenced.

This is an inspection checklist for a part with hundreds of measurements and the data is pulled from about 7 different spreadsheets. The spec requires each value to have an inspection method to go along with it. I realize I can do it individually, however, of course, the cells are referencing a different cell which would take customized IF statement for each which would not cut down on any time. I am trying to take this statement and apply it to dozens of other spreadsheets moving forward or else manual entry wouldn't be all too bad.

For example: If the cell references a value from the Hard Gauge workbook, I would like the cell to the right of it to read "Hard Gauge," and if the cell is referencing a Height Gauge value, I would like the cell to the right of it to read "Height Gauge."

Something like:

IF({File Path}[40452-1016 REV. A **(HEIGHT GAGE)**.xlsm] = TRUE, "HEIGHT GAUGE", IF({File Path}[40452-1016 REV. A **(HARD GAGE)**.xlsm] = TRUE, "HARD GAUGE",,))

I know the issue will be to ignore the cell value that comes after the ".xlsm"

{File Path}[40452-1016 REV. A (HEIGHT GAGE).xlsm]**'!$C$29**

Can someone help me or let me know if this is even a possibility. It doesn't necessarily have to be from an IF statement, a VBA macro will work just fine.

Upvotes: 0

Views: 103

Answers (1)

Preston
Preston

Reputation: 8177

This will give you everything to the left of .xlsm, if indeed the string contains .xlsm. That should be a good starting point

Sub t()
Dim endrow As Long
Dim column As String
Dim a As Range

column = "A"
endrow = ActiveSheet.Range(column & Rows.Count).End(xlUp).Row


For Each a In Range(column & endrow)
    If InStr(1, a.Value, ".xlsm", vbTextCompare) > 0 Then
        a.Offset(0, 1).Value = Left(a.Value, InStr(1, a.Value, ".xlsm", vbTextCompare))
    End If
Next a
End Sub

Upvotes: 1

Related Questions