Macros help

[Minhaj]

Dear All,

The following code generated numerical value on the on the BILL1 SHEET from MECH SHET. I am interested to know, is it possible to get cell reference number in the BILL1 SHEET? I mean instead of numerical value I want cell reference as "=mech!A1"

Sub BillFill1()
On Error Resume Next
Dim Dept_Row As Long
Dim Dept_Clm As Long
Table1 = Sheets("bill1").Range("A4:A531") ' Sheet1 - sheet name, BOQ
Table2 = Sheets("mech").Range("D12:M531") ' Sheet 2 - sheet name, cost sheet
Dept_Row = Sheets("bill1").Range("D4").Row ' Change E3 with the cell from where you need to start populating the Department
Dept_Clm = Sheets("bill1").Range("D4").Column
For Each cl In Table1
Sheets("bill1").Cells(Dept_Row, Dept_Clm) = Application.WorksheetFunction.VLookup(cl, Table2, 10, False)
Dept_Row = Dept_Row + 1
Next cl
MsgBox "Done"
End Sub

 

[kchiba]

Hi Minhaj,

Here is the code to find an address and use it in a formula.

Sub BuildFormula()

    With Sheets("Mech")
       Set rFound = .Columns(4).Find(What:="aaa", After:=.Cells(1, 4), _
       LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
       SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
   
    cADD = rFound.Address
      rAdd = Range(cADD).Row
    End With
    Sheets("Bill1").Range("D4").Formula = "=Mech!M" & rAdd
   
End Sub

You will note that all parameters are hard-coded including the What in the find formula, adapt this and use it in your VBA

 

[Minhaj]

Hi Minhaj,

Here is the code to find an address and use it in a formula.

Sub BuildFormula()

    With Sheets("Mech")
       Set rFound = .Columns(4).Find(What:="aaa", After:=.Cells(1, 4), _
       LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
       SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
  
    cADD = rFound.Address
      rAdd = Range(cADD).Row
    End With
    Sheets("Bill1").Range("D4").Formula = "=Mech!M" & rAdd
  
End Sub

You will note that all parameters are hard-coded including the What in the find formula, adapt this and use it in your VBA

Dear K Chiba,

Thank you so much for your kind help, you solved mine big headache...Thanx again...