SOLVED

Contributor

# I need help with convert formulas to vba codes

I not sure how  to convert formulas to VBA Codes.

Thanks You

Thomas

11 Replies
best response confirmed by sf49ers (Contributor)
Solution

# Re: I need help with convert formulas to vba codes

``````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``````

# Re: I need help with convert formulas to vba codes

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

# Re: I need help with convert formulas to vba codes

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?

# Re: I need help with convert formulas to vba codes

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

# Re: I need help with convert formulas to vba codes

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

# Re: I need help with convert formulas to vba codes

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

# Re: I need help with convert formulas to vba codes

Yes, it converts formulas to their corresponding values.

# Re: I need help with convert formulas to vba codes

some of code is red on my end

# Re: I need help with convert formulas to vba codes

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

Thanks @DKoontz!

# Re: I need help with convert formulas to vba codes

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