VBA lookup cell address with Match inside macro to place a variable number

[Rlumpy]

Hello, I am fairly new to VBA and have spent 3 days trying to figure this out. Seems like it should be simple but I can't find much online for my specific task.
I'm trying to find if a pipe is out of roundness (Ovality) and I am using this to Enter 4 radius dimensions, add them for diameters and perform the Dmax-Dmin/Dmax+Dmin * 2
I was trying to use the PIPE variable to lookup the cell address in my Column A:A for ROW, and place OvMath number into (that lookup ROW, column AJ) but the syntax/coding boggles me.
I can't find much online to show me proper wording to use inside VBA which works perfectly in plain Excel.
The issues I would hope someone can help is with line : Range(ADDRESS(MATCH(Pipe, Range("A:A"),0),COLUMN("AJ:AJ")).value = and I can place the OvMath into this from inside this macro, I would not have to insert anything into Excel and then recall it.

I hope this makes sense and that someone can help me write: Lookup PIPE's ROW in column A:A and then place OvMath into that lookup ROW and Column AJ using VBA coding.
______________________________________________________
Private Sub cb_OK_Click()
Range("BI2:BI7").Clear
Dim Pipe As Integer, Top As Double, Bot As Double, Lf As Double, Rt As Double, dX As Double, dY As Double, OvCell As String, OvMath As Double
Pipe = Me.t_Pipe.Value
Top = Me.t_Top.Value
Bot = Me.t_Bot.Value
Lf = Me.t_Lf.Value
Rt = Me.t_Rt.Value
dX = Lf + Rt
dY = Top + Bot
OvMath = 2 * ((WorksheetFunction.Max(dX, dY) - WorksheetFunction.Min(dX, dY)) / (WorksheetFunction.Max(dX, dY) + WorksheetFunction.Min(dX, dY)))
'OvCell = Range(WorksheetFunction.VLookup(Pipe, Range("A16:A200"), 1, False))
'these values I just placed in the worksheet to check that the math was working except for PIPE, I had to use it for Cell Address Lookup
Range("BI4").Value = Pipe
Range("BI5").Value = dX
Range("BI6").Value = dY
Range("BI7").Value = OvMath
'If I can figure out the lower formula to use in the VBA I won't have to insert and call back anything from Excel sheet
OvCell = Range("BI8").Value
Range(OvCell).Value = OvMath
'This line is from below trying to test my scenario
'Range(ADDRESS(MATCH(Pipe, Range("A:A"),0),COLUMN("AJ:AJ")).value = 2 * ((WorksheetFunction.Max(dX, dY) - WorksheetFunction.Min(dX, dY)) / (WorksheetFunction.Max(dX, dY) + WorksheetFunction.Min(dX, dY)))

'this next line's part before the "=" is from a recorded macro that doesn't seem to help me any, I tried to edit it above but it won't take.
'ADDRESS(MATCH(R[-8]C[-9],C[-60],0),COLUMN(C[-25])) = 2 * ((WorksheetFunction.Max(dX, dY) - WorksheetFunction.Min(dX, dY)) / (WorksheetFunction.Max(dX, dY) + WorksheetFunction.Min(dX, dY)))
Unload Me
End Sub
_____________________
Thank you very much.

 

[severynm]

Welcome!

Range(ADDRESS(MATCH(Pipe, Range("A:A"),0),COLUMN("AJ:AJ")).value

This definitely looks weird to me too. Correct me if I'm wrong, but it looks like you're searching for a value in column A, and then writing the result of your equation to the corresponding row in column AJ.

In my opinion a much more readable way to do that is to use .Cells which allows you to specify a row and column as a number. So, find the row of your match in column A, and then write to the cell in the matched row in column 36. Try starting with

    Dim rowMatch As Long
    rowMatch= Application.WorksheetFunction.Match(Pipe, SheetName.Range("A:A"), 0)
    SheetName.Cells(rowMatch, 36).Value = "... equation here ..." 'column 36 = column AJ

 

[Rlumpy]

Welcome!

This definitely looks weird to me too. Correct me if I'm wrong, but it looks like you're searching for a value in column A, and then writing the result of your equation to the corresponding row in column AJ.

In my opinion a much more readable way to do that is to use .Cells which allows you to specify a row and column as a number. So, find the row of your match in column A, and then write to the cell in the matched row in column 36. Try starting with

    Dim rowMatch As Long
    rowMatch= Application.WorksheetFunction.Match(Pipe, SheetName.Range("A:A"), 0)
    SheetName.Cells(rowMatch, 36).Value = "... equation here ..." 'column 36 = column AJ

Yes that is exactly what I need, I just had no idea how to change the formula which seemed straight forward in Excel over to VBA code.
Thank you very much for your help.