Aug 13 2021 06:43 PM - edited Aug 13 2021 07:13 PM
Aug 13 2021 11:24 PM
SolutionPlease 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
Aug 14 2021 09:19 AM - edited Aug 14 2021 09:21 AM
Aug 14 2021 09:19 AM - edited Aug 14 2021 09:21 AM
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
Aug 14 2021 07:22 PM
Aug 15 2021 09:03 AM - edited Aug 22 2021 11:36 PM
Aug 15 2021 09:03 AM - edited Aug 22 2021 11:36 PM
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
Sep 15 2021 04:51 PM
Sep 15 2021 04:58 PM
Sep 15 2021 05:13 PM
Yes, it converts formulas to their corresponding values.
Sep 15 2021 05:47 PM
Sep 16 2021 08:41 AM
Sep 16 2021 09:16 AM
That may be due to the editor's settings perhaps. If the code works as expected, nothing to worry about then.
Aug 13 2021 11:24 PM
SolutionPlease 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