Translate Index Match formula to VBA

[Mikeymike_W]

Hi All,

I have a formula which i want to remove from my sheet and place in a userform so it is only active when a command button is pressed.

The formula is:

=IFERROR(IF((INDEX(FIPCDNumber1,MATCH(PID,PIDFIP2,0)))=0,"",(INDEX(FIPCDNumber1,MATCH(PID,PIDFIP2,0)))),"")

Big thanks for any help you can provide

 

[Mikeymike_W]

Hi Jeff,

I doubt youre being dense, im probably just rubbish at explaining. Excel isnt one of my strong points.

FIPCDNumber1 refers to a column which contains blank cells as well as a list of ID markers:

CD001
CD002
CD003 etc

PIDFIP2 again refers to a named column of data which will have ID MArkers:

GH010126
RT051256
SS120845 etc

These are the values i am looking to match with the "PID" field on the CRF

Many thanks,

Mike

 

[Jeffrey Mahoney]

**Light bulb goes on**

working...

 

[Mikeymike_W]

Thanks Jeff?

 

[Jeffrey Mahoney]

Mike,

So here's a Function that you can call from a cell like: =GetFIPCD() Or you can call the function from your form by: TextBox1.value = GetFIPCD

The question for me was that the value of PID is currently being retrieved from a cell. Is that the way you want it.?

Function GetFIPCD()
  Dim FIP As Variant
  Dim FIPCDNumber1 As Range
  Dim PID As Range
  Dim PIDFIP2 As Range
  
  Application.Volatile
  
  Set FIPCDNumber1 = Range("FIPCDNumber1")
  Set PID = Range("PID")
  Set PIDFIP2 = Range("PIDFIP2")
  
  FIP = Application.Index(FIPCDNumber1, Application.Match(PID, PIDFIP2, 0))
  If IsError(FIP) = True Then
    GetFIPCD = ""
  ElseIf Len(FIP) = 0 Then
    GetFIPCD = ""
  Else
    GetFIPCD = FIP
  End If
  
  
End Function

 

[Mikeymike_W]

Hi Jeff,

I just tried this out and it works beautifully!!
I just referenced it in my userform as you suggested and it works like magic.

I cant thank you enough for all your hard work to help me out, its greatly appreciated!

Hope youre having a good one,

Mike

 

[Mikeymike_W]

Hi Jeff,

I just encountered an issue and i'm hoping you can help.

The code you provided will only work if i have the workbook open that the code is searching i.e. "FIP"

Is there a way to open this in the background as read only so the user isnt aware its happening and also if someone else is using the workbook then there wont be interference?

Thanks in advance for any help,

Mike

 

[Mikeymike_W]

Hi Jeff,

Not to worry managed to sort it.

I created the following function:

Function WorkbookIsOpen(wbname) As Boolean
'Returns TRUE if the workbook is open
Dim x As Workbook

On Error Resume Next

Set x = Workbooks(wbname)

If Err = 0 Then WorkbookIsOpen = True _
Else WorkbookIsOpen = False

End Function

Then just added the following to my command button code

Application.DisplayAlerts = False
Application.ScreenUpdating = False
If WorkbookIsOpen("FIP.xlsm") Then
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Exit Sub
Else
Workbooks.Open Filename:="workbook location",UpdateLinks:=True, ReadOnly:=True
ActiveWindow.Visible = False
ThisWorkbook.activate
End If

Thanks again for your help,

Mike