Forum Discussion

sf49ers19238597's avatar
sf49ers19238597
Iron Contributor
Aug 14, 2021
Solved

I need help with convert formulas to vba codes

I not sure how  to convert formulas to VBA Codes.  

 

Thanks You 

Thomas

excel
Formulas and Functions
Macros and VBA
  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Aug 14, 2021

    sf49ers19238597 

    Please give this a try...

     

    Sub InsertFormulas()
Dim wsPTW4  As Worksheet
Dim lr      As Long

Application.ScreenUpdating = False

Set wsPTW4 = ThisWorkbook.Worksheets("PICK THEM WEEK 4")
lr = wsPTW4.Cells(Rows.Count, 1).End(xlUp).Row

'Formula for Column C
With wsPTW4.Range("C3:C" & lr)
    .Formula = "=IF('4'!D2<>"""",'4'!D2,""BYE WEEK TEAM"")"
    .Value = .Value
End With

'Formula for Column D
With wsPTW4.Range("D3:D" & lr)
    .Formula = "=VLOOKUP(C3,INPUT_SCORES!$A$2:$R$33,5,0)"
    .Value = .Value
End With

'Formula for Column G
With wsPTW4.Range("G3:G" & lr)
    .Formula = "=IF('4'!G2<>"""",'4'!G2,""BYE WEEK TEAM"")"
    .Value = .Value
End With

'Formula for Column H
With wsPTW4.Range("H3:H" & lr)
    .Formula = "=VLOOKUP(G3,INPUT_SCORES!$A$2:$R$33,5,0)"
    .Value = .Value
End With

'Formula for Column L
With wsPTW4.Range("L12:L17")
    .Formula = "=IF('BYE WEEKS'!K2<>"""",'BYE WEEKS'!K2,"""")"
    .Value = .Value
End With
Application.ScreenUpdating = True
End Sub

11 Replies

  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Silver Contributor
    Aug 14, 2021

    sf49ers19238597 

    Please give this a try...

     

    Sub InsertFormulas()
Dim wsPTW4  As Worksheet
Dim lr      As Long

Application.ScreenUpdating = False

Set wsPTW4 = ThisWorkbook.Worksheets("PICK THEM WEEK 4")
lr = wsPTW4.Cells(Rows.Count, 1).End(xlUp).Row

'Formula for Column C
With wsPTW4.Range("C3:C" & lr)
    .Formula = "=IF('4'!D2<>"""",'4'!D2,""BYE WEEK TEAM"")"
    .Value = .Value
End With

'Formula for Column D
With wsPTW4.Range("D3:D" & lr)
    .Formula = "=VLOOKUP(C3,INPUT_SCORES!$A$2:$R$33,5,0)"
    .Value = .Value
End With

'Formula for Column G
With wsPTW4.Range("G3:G" & lr)
    .Formula = "=IF('4'!G2<>"""",'4'!G2,""BYE WEEK TEAM"")"
    .Value = .Value
End With

'Formula for Column H
With wsPTW4.Range("H3:H" & lr)
    .Formula = "=VLOOKUP(G3,INPUT_SCORES!$A$2:$R$33,5,0)"
    .Value = .Value
End With

'Formula for Column L
With wsPTW4.Range("L12:L17")
    .Formula = "=IF('BYE WEEKS'!K2<>"""",'BYE WEEKS'!K2,"""")"
    .Value = .Value
End With
Application.ScreenUpdating = True
End Sub
    • DKoontz's avatar
      DKoontz
      Iron Contributor
      Sep 15, 2021
      Is the .Value = .Value used within a With block used to value it out?
      • Subodh_Tiwari_sktneer's avatar
        Subodh_Tiwari_sktneer
        Silver Contributor
        Aug 15, 2021
        Your question was, how to place formulas with VBA and I provided the solution for that.

        So now, do you want to remove a team from column C if the corresponding cell in column D says 'Bye' and remove a team from column G if the corresponding cell in column H says 'Bye' in the end?

        Also, do you want to place the text 'BYE WEEK TEAMS' in the end? e.g. what you showed in cells C17 and G17 on 'HERE SHOULD WANT LOOK LIKE' Sheet?

Resources