I need help with convert formulas to vba codes

[sf49ers19238597]

I not sure how  to convert formulas to VBA Codes.  

Thanks You 

Thomas

[#2 · Accepted solution]

@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

[#3]

@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

[#4]

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?

[#5]

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

[#6]

can put notes in code the do I can not configure out what codes id doing

[#7]

Is the .Value = .Value used within a With block used to value it out?

[#8]

@DKoontz 

Yes, it converts formulas to their corresponding values.

[#9]

some of code is red on my end

[#10]

Awesome! I was pasting as value lol, this is much cleaner.

[#11]

Thanks @DKoontz!

[#12]

@sf49ers19238597 

That may be due to the editor's settings perhaps. If the code works as expected, nothing to worry about then.