VBA to place formula based on cell value-CodePudding

i need help, here I am facing an issue with my code first issue is it give me compile error also it is selecting the source sheet but when i define cell reference along with sheet reference it is not selecting the respective cell

all i want to do is check if cell H5 of sheet "POWER BI" is "ETA1" then select cell "U6" in sheet "Source" else if it is "ETA2" then select "AB6" of sheet "Source" and so on till 12

when it finds the respective cell then place the first given formula in that cell 2nd formula in its adjacent cell and the third formula adjacent to 2nd formula cell

1st formula:

FormulaR1C1 = "=IFERROR(INDEX(CCC_[ETA1],MATCH([@[Purchasing Document50]],CCC_[Purchasing Document38],0)),"""")"

2nd formula:

FormulaR1C1 = "=IFERROR(INDEX(CCC_[ETD],MATCH([@[Purchasing Document50]],CCC_[Purchasing Document38],0)),"""")"

3rd formula:

FormulaR1C1 = "=IFERROR(INDEX(CCC_[VESSEL],MATCH([@[Purchasing Document50]],CCC_[Purchasing Document38],0)),"""")"

Sub placeETA()

Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("POWER BI")

Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("Source")

ws1.Select

If ws1.Range("H5").Value = "ETA1" Then ws2.Range("U6").Select

ElseIf ws1.Range("H5").Value = "ETA2" Then ws2.Range("AB6").Select

ElseIf ws1.Range("H5").Value = "ETA3" Then ws2.Range("AI6").Select

ElseIf ws1.Range("H5").Value = "ETA4" Then ws2.Range("AP6").Select

ElseIf ws1.Range("H5").Value = "ETA5" Then ws2.Range("AW6").Select

ElseIf ws1.Range("H5").Value = "ETA6" Then ws2.Range("BD6").Select

ElseIf ws1.Range("H5").Value = "ETA7" Then ws2.Range("BK6").Select

ElseIf ws1.Range("H5").Value = "ETA8" Then ws2.Range("BR6").Select

ElseIf ws1.Range("H5").Value = "ETA9" Then ws2.Range("BY6").Select

ElseIf ws1.Range("H5").Value = "ETA10" Then ws2.Range("CF6").Select

ElseIf ws1.Range("H5").Value = "ETA11" Then ws2.Range("CM6").Select

ElseIf ws1.Range("H5").Value = "ETA12" Then ws2.Range("CT6").Select

Else

End If 

End Sub

CodePudding user response：

Please, try the next updated code. I think no need of any selection. Select, Activate only consume Excel resources, not bringing any benefit:


Sub placeETA()
 Dim formula1 As String, formula2 As String, formula3 As String, ws1 As Worksheet, ws2 As Worksheet, specCell As Range
 
 formula1 = "=IFERROR(INDEX(CCC_[ETA1],MATCH([@[Purchasing Document50]],CCC_[Purchasing Document38],0)),"""")"
 formula2 = "=IFERROR(INDEX(CCC_[ETD],MATCH([@[Purchasing Document50]],CCC_[Purchasing Document38],0)),"""")"
 formula3 = "=IFERROR(INDEX(CCC_[VESSEL],MATCH([@[Purchasing Document50]],CCC_[Purchasing Document38],0)),"""")"
 
 Set ws1 = ThisWorkbook.Sheets("POWER BI")
 Set ws2 = ThisWorkbook.Sheets("Source")


If ws1.Range("H5").value = "ETA1" Then
        Set specCell = ws2.Range("U6")
ElseIf ws1.Range("H5").value = "ETA2" Then
         Set specCell = ws2.Range("AB6")
ElseIf ws1.Range("H5").value = "ETA3" Then
        Set specCell = ws2.Range("AI6")
ElseIf ws1.Range("H5").value = "ETA4" Then
        Set specCell = ws2.Range("AP6")
ElseIf ws1.Range("H5").value = "ETA5" Then
        Set specCell = ws2.Range("AW6")
ElseIf ws1.Range("H5").value = "ETA6" Then
        Set specCell = ws2.Range("BD6")
ElseIf ws1.Range("H5").value = "ETA7" Then
        Set specCell = ws2.Range("BK6")
ElseIf ws1.Range("H5").value = "ETA8" Then
        Set specCell = ws2.Range("BR6")
ElseIf ws1.Range("H5").value = "ETA9" Then
        Set specCell = ws2.Range("BY6")
ElseIf ws1.Range("H5").value = "ETA10" Then
        Set specCell = ws2.Range("CF6")
ElseIf ws1.Range("H5").value = "ETA11" Then
        Set specCell = ws2.Range("CM6")
ElseIf ws1.Range("H5").value = "ETA12" Then
        Set specCell = ws2.Range("CT6")
Else
End If
   specCell.Formula = formula1
   specCell.Offset(, 1).Formula = formula2
   specCell.Offset(, 2).Formula = formula3
End Sub

Not tested but if I correctly understood your question, it should work...

Edited:

The next version accepts different formula for each case:

Sub placeETASpec()
 Dim ws1 As Worksheet, ws2 As Worksheet, specCell As Range
 
 Set ws1 = ThisWorkbook.Sheets("POWER BI")
 Set ws2 = ThisWorkbook.Sheets("Source")

 If ws1.Range("H5").value = "ETA1" Then
        Set specCell = ws2.Range("U6")
        With specCell
            .Formula = "=IFERROR(INDEX(CCC_[ETA1],MATCH([@[Purchasing Document50]],CCC_[Purchasing Document38],0)),"""")"
            .Offset(, 1).Formula = "=IFERROR(INDEX(CCC_[ETD],MATCH([@[Purchasing Document50]],CCC_[Purchasing Document38],0)),"""")"
            .Offset(, 2).Formula = "=IFERROR(INDEX(CCC_[VESSEL],MATCH([@[Purchasing Document50]],CCC_[Purchasing Document38],0)),"""")"
        End With
 ElseIf ws1.Range("H5").value = "ETA2" Then
         Set specCell = ws2.Range("AB6")
         With specCell
            .Formula = "=IFERROR(INDEX(CCC_[ETA2],MATCH([@[Purchasing Document50]],CCC_[Purchasing Document38],0)),"""")"
            .Offset(, 1).Formula = "=IFERROR(INDEX(CCC_[ETD],MATCH([@[Purchasing Document50]],CCC_[Purchasing Document38],0)),"""")"
            .Offset(, 2).Formula = "=IFERROR(INDEX(CCC_[VESSEL],MATCH([@[Purchasing Document50]],CCC_[Purchasing Document38],0)),"""")"
        End With
 ElseIf ws1.Range("H5").value = "ETA3" Then
        Set specCell = ws2.Range("AI6")
        'and so on in the rest of the cases...
        
 ElseIf ws1.Range("H5").value = "ETA4" Then
        Set specCell = ws2.Range("AP6")
 ElseIf ws1.Range("H5").value = "ETA5" Then
        Set specCell = ws2.Range("AW6")
 ElseIf ws1.Range("H5").value = "ETA6" Then
        Set specCell = ws2.Range("BD6")
 ElseIf ws1.Range("H5").value = "ETA7" Then
        Set specCell = ws2.Range("BK6")
 ElseIf ws1.Range("H5").value = "ETA8" Then
        Set specCell = ws2.Range("BR6")
 ElseIf ws1.Range("H5").value = "ETA9" Then
        Set specCell = ws2.Range("BY6")
 ElseIf ws1.Range("H5").value = "ETA10" Then
        Set specCell = ws2.Range("CF6")
 ElseIf ws1.Range("H5").value = "ETA11" Then
        Set specCell = ws2.Range("CM6")
 ElseIf ws1.Range("H5").value = "ETA12" Then
        Set specCell = ws2.Range("CT6")
 Else
 End If
End Sub

I do not know if the formula I imagined in the second case is the one you look for. If yes, you can keep the initial case but adapting the formula to change "ETA1" with the appropriate one. Even if the change may more complex, if you can describe the logic behind the changing algorithm I can try adapting the formula for each case...