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
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?
      • DKoontz's avatar
        DKoontz
        Iron Contributor
        Sep 16, 2021
        Awesome! I was pasting as value lol, this is much cleaner.
  • sf49ers19238597's avatar
    sf49ers19238597
    Iron Contributor
    Aug 14, 2021

    Subodh_Tiwari_sktneer 

     

    The code works. but look bye in column D & H.

     

     

    I added other worksheet  that I want look  when  look  Column L see is any team in.  column Colums C,D & G,H

     

    week 4 might be more in years become

     

    • 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?
      • sf49ers19238597's avatar
        sf49ers19238597
        Iron Contributor
        Aug 15, 2021

         

         

        yes remove from C11:D11 & G11:H11. Put BYE WEEK TEAMS in last cell or cells after such like 

        The worksheet call should want look like. it can be 6 teams in column L in future



        Thanks You
        Thomas

Resources