How convert all formulas to VBA codes

TLS49

Board Regular
Joined
Nov 26, 2019
Messages
98
Office Version
  1. 365
Platform
  1. Windows
How convert this formulas in VBA Codes with notes for each section

DOWNLOAD NFL SCHEDULE FILE HERE

VBA Code:
'ROAD TEAMS NAMES C3:C18        
=IF('1'!D2<>"",'1'!D2,"BYE WEEK TEAM")
=IF('2'!D2<>"",'2'!D2,"BYE WEEK TEAM")
=IF('3'!D2<>"",'3'!D2,"BYE WEEK TEAM")
=IF('4'!D2<>"",'4'!D2,"BYE WEEK TEAM")
=IF('5'!D2<>"",'5'!D2,"BYE WEEK TEAM")
=IF('6'!D2<>"",'6'!D2,"BYE WEEK TEAM")
=IF('7'!D2<>"",'7'!D2,"BYE WEEK TEAM")
=IF('8'!D2<>"",'8'!D2,"BYE WEEK TEAM")
=IF('9'!D2<>"",'9'!D2,"BYE WEEK TEAM")
=IF('10'!D2<>"",'10'!D2,"BYE WEEK TEAM")
=IF('11'!D2<>"",'11'!D2,"BYE WEEK TEAM")
=IF('12'!D2<>"",'12'!D2,"BYE WEEK TEAM")
=IF('13'!D2<>"",'13'!D2,"BYE WEEK TEAM")
=IF('14'!D2<>"",'14'!D2,"BYE WEEK TEAM")
=IF('15'!D2<>"",'15'!D2,"BYE WEEK TEAM")
=IF('16'!D2<>"",'16'!D2,"BYE WEEK TEAM")
=IF('17'!D2<>"",'17'!D2,"BYE WEEK TEAM")
=IF('18'!D2<>"",'18'!D2,"BYE WEEK TEAM")

'HOME TEAMS NAMES G3:G18
=IF('1'!G2<>"",'1'!G2,"BYE WEEK TEAM")
=IF('2'!G2<>"",'2'!G2,"BYE WEEK TEAM")
=IF('3'!G2<>"",'3'!G2,"BYE WEEK TEAM")
=IF('4'!G2<>"",'4'!G2,"BYE WEEK TEAM")
=IF('5'!G2<>"",'5'!G2,"BYE WEEK TEAM")
=IF('6'!G2<>"",'6'!G2,"BYE WEEK TEAM")
=IF('7'!G2<>"",'7'!G2,"BYE WEEK TEAM")
=IF('8'!G2<>"",'8'!G2,"BYE WEEK TEAM")
=IF('9'!G2<>"",'9'!G2,"BYE WEEK TEAM")
=IF('10'!G2<>"",'10'!G2,"BYE WEEK TEAM")
=IF('11'!G2<>"",'11'!G2,"BYE WEEK TEAM")
=IF('12'!G2<>"",'12'!G2,"BYE WEEK TEAM")
=IF('13'!G2<>"",'13'!G2,"BYE WEEK TEAM")
=IF('14'!G2<>"",'14'!G2,"BYE WEEK TEAM")
=IF('15'!G2<>"",'15'!G2,"BYE WEEK TEAM")
=IF('16'!G2<>"",'16'!G2,"BYE WEEK TEAM")
=IF('17'!G2<>"",'17'!G2,"BYE WEEK TEAM")
=IF('18'!G2<>"",'18'!G2,"BYE WEEK TEAM")



'PICK THEM WEEK 1  L10:L15
=IF('BYE WEEKS'!B3<>"",'BYE WEEKS'!B3,"")
=IF('BYE WEEKS'!B4<>"",'BYE WEEKS'!B4,"")
=IF('BYE WEEKS'!B5<>"",'BYE WEEKS'!B5,"")
=IF('BYE WEEKS'!B6<>"",'BYE WEEKS'!B6,"")
=IF('BYE WEEKS'!B7<>"",'BYE WEEKS'!B7,"")
=IF('BYE WEEKS'!B8<>"",'BYE WEEKS'!B8,"")

'PICK THEM WEEK 2 L10:L15
=IF('BYE WEEKS'!E3<>"",'BYE WEEKS'!E3,"")
=IF('BYE WEEKS'!E4<>"",'BYE WEEKS'!E4,"")
=IF('BYE WEEKS'!E5<>"",'BYE WEEKS'!E5,"")
=IF('BYE WEEKS'!E6<>"",'BYE WEEKS'!E6,"")
=IF('BYE WEEKS'!E7<>"",'BYE WEEKS'!E7,"")
=IF('BYE WEEKS'!E8<>"",'BYE WEEKS'!E8,"")

'PICK THEM WEEK 3 L10:L15
=IF('BYE WEEKS'!H3<>"",'BYE WEEKS'!H3,"")
=IF('BYE WEEKS'!H4<>"",'BYE WEEKS'!H4,"")
=IF('BYE WEEKS'!H5<>"",'BYE WEEKS'!H5,"")
=IF('BYE WEEKS'!H6<>"",'BYE WEEKS'!H6,"")
=IF('BYE WEEKS'!H7<>"",'BYE WEEKS'!H7,"")
=IF('BYE WEEKS'!H8<>"",'BYE WEEKS'!H8,"")

'PICK THEM WEEK 4 L10:L15
=IF('BYE WEEKS'!K3<>"",'BYE WEEKS'!K3,"")
=IF('BYE WEEKS'!K4<>"",'BYE WEEKS'!K4,"")
=IF('BYE WEEKS'!K5<>"",'BYE WEEKS'!K5,"")
=IF('BYE WEEKS'!K6<>"",'BYE WEEKS'!K6,"")
=IF('BYE WEEKS'!K7<>"",'BYE WEEKS'!K7,"")
=IF('BYE WEEKS'!K8<>"",'BYE WEEKS'!K8,"")

'PICK THEM WEEK 5 L10:L15
=IF('BYE WEEKS'!N3<>"",'BYE WEEKS'!N3,"")
=IF('BYE WEEKS'!N4<>"",'BYE WEEKS'!N4,"")
=IF('BYE WEEKS'!N5<>"",'BYE WEEKS'!N5,"")
=IF('BYE WEEKS'!N6<>"",'BYE WEEKS'!N6,"")
=IF('BYE WEEKS'!N7<>"",'BYE WEEKS'!N7,"")
=IF('BYE WEEKS'!N8<>"",'BYE WEEKS'!N8,"")

'PICK THEM WEEK 6  L10:L15
=IF('BYE WEEKS'!Q3<>"",'BYE WEEKS'!Q3,"")
=IF('BYE WEEKS'!Q4<>"",'BYE WEEKS'!Q4,"")
=IF('BYE WEEKS'!Q5<>"",'BYE WEEKS'!Q5,"")
=IF('BYE WEEKS'!Q6<>"",'BYE WEEKS'!Q6,"")
=IF('BYE WEEKS'!Q7<>"",'BYE WEEKS'!Q7,"")
=IF('BYE WEEKS'!Q8<>"",'BYE WEEKS'!Q8,"")

'PICK THEM WEEK 7  L10:L15
=IF('BYE WEEKS'!B11<>"",'BYE WEEKS'!B11,"")
=IF('BYE WEEKS'!B12<>"",'BYE WEEKS'!B12,"")
=IF('BYE WEEKS'!B13<>"",'BYE WEEKS'!B13,"")
=IF('BYE WEEKS'!B14<>"",'BYE WEEKS'!B14,"")
=IF('BYE WEEKS'!B15<>"",'BYE WEEKS'!B15,"")
=IF('BYE WEEKS'!B16<>"",'BYE WEEKS'!B16,"")

'PICK THEM WEEK 8  L10:L15
=IF('BYE WEEKS'!E11<>"",'BYE WEEKS'!E11,"")
=IF('BYE WEEKS'!E12<>"",'BYE WEEKS'!E12,"")
=IF('BYE WEEKS'!E13<>"",'BYE WEEKS'!E13,"")
=IF('BYE WEEKS'!E14<>"",'BYE WEEKS'!E14,"")
=IF('BYE WEEKS'!E15<>"",'BYE WEEKS'!E15,"")
=IF('BYE WEEKS'!E16<>"",'BYE WEEKS'!E16,"")

'PICK THEM WEEK 9  L10:L15
=IF('BYE WEEKS'!H11<>"",'BYE WEEKS'!H11,"")
=IF('BYE WEEKS'!H12<>"",'BYE WEEKS'!H12,"")
=IF('BYE WEEKS'!H13<>"",'BYE WEEKS'!H13,"")
=IF('BYE WEEKS'!H14<>"",'BYE WEEKS'!H14,"")
=IF('BYE WEEKS'!H15<>"",'BYE WEEKS'!H15,"")
=IF('BYE WEEKS'!H16<>"",'BYE WEEKS'!H16,"")

'PICK THEM WEEK 10  L10:L15
=IF('BYE WEEKS'!K11<>"",'BYE WEEKS'!K11,"")
=IF('BYE WEEKS'!K12<>"",'BYE WEEKS'!K12,"")
=IF('BYE WEEKS'!K13<>"",'BYE WEEKS'!K13,"")
=IF('BYE WEEKS'!K14<>"",'BYE WEEKS'!K14,"")
=IF('BYE WEEKS'!K15<>"",'BYE WEEKS'!K15,"")
=IF('BYE WEEKS'!K16<>"",'BYE WEEKS'!K16,"")

'PICK THEM WEEK 11  L10:L15
=IF('BYE WEEKS'!N11<>"",'BYE WEEKS'!N11,"")
=IF('BYE WEEKS'!N12<>"",'BYE WEEKS'!N12,"")
=IF('BYE WEEKS'!N13<>"",'BYE WEEKS'!N13,"")
=IF('BYE WEEKS'!N14<>"",'BYE WEEKS'!N14,"")
=IF('BYE WEEKS'!N15<>"",'BYE WEEKS'!N15,"")
=IF('BYE WEEKS'!N16<>"",'BYE WEEKS'!N16,"")

'PICK THEM WEEK 12  L10:L15
=IF('BYE WEEKS'!Q11<>"",'BYE WEEKS'!Q11,"")
=IF('BYE WEEKS'!Q12<>"",'BYE WEEKS'!Q12,"")
=IF('BYE WEEKS'!Q13<>"",'BYE WEEKS'!Q13,"")
=IF('BYE WEEKS'!Q14<>"",'BYE WEEKS'!Q14,"")
=IF('BYE WEEKS'!Q15<>"",'BYE WEEKS'!Q15,"")
=IF('BYE WEEKS'!Q16<>"",'BYE WEEKS'!Q16,"")

'PICK THEM WEEK 13  L10:L15
=IF('BYE WEEKS'!B19<>"",'BYE WEEKS'!B19,"")
=IF('BYE WEEKS'!B20<>"",'BYE WEEKS'!B20,"")
=IF('BYE WEEKS'!B21<>"",'BYE WEEKS'!B21,"")
=IF('BYE WEEKS'!B22<>"",'BYE WEEKS'!B22,"")
=IF('BYE WEEKS'!B23<>"",'BYE WEEKS'!B23,"")
=IF('BYE WEEKS'!B24<>"",'BYE WEEKS'!B24,"")

'PICK THEM WEEK 14  L10:L15
=IF('BYE WEEKS'!E19<>"",'BYE WEEKS'!E19,"")
=IF('BYE WEEKS'!E20<>"",'BYE WEEKS'!E20,"")
=IF('BYE WEEKS'!E21<>"",'BYE WEEKS'!E21,"")
=IF('BYE WEEKS'!E22<>"",'BYE WEEKS'!E22,"")
=IF('BYE WEEKS'!E23<>"",'BYE WEEKS'!E23,"")
=IF('BYE WEEKS'!E24<>"",'BYE WEEKS'!E24,"")

'PICK THEM WEEK 15  L10:L15
=IF('BYE WEEKS'!H19<>"",'BYE WEEKS'!H19,"")
=IF('BYE WEEKS'!H20<>"",'BYE WEEKS'!H20,"")
=IF('BYE WEEKS'!H21<>"",'BYE WEEKS'!H21,"")
=IF('BYE WEEKS'!H22<>"",'BYE WEEKS'!H22,"")
=IF('BYE WEEKS'!H23<>"",'BYE WEEKS'!H23,"")
=IF('BYE WEEKS'!H24<>"",'BYE WEEKS'!H24,"")

'PICK THEM WEEK 16  L10:L15
=IF('BYE WEEKS'!K19<>"",'BYE WEEKS'!K19,"")
=IF('BYE WEEKS'!K20<>"",'BYE WEEKS'!K20,"")
=IF('BYE WEEKS'!K21<>"",'BYE WEEKS'!K21,"")
=IF('BYE WEEKS'!K22<>"",'BYE WEEKS'!K22,"")
=IF('BYE WEEKS'!K23<>"",'BYE WEEKS'!K23,"")
=IF('BYE WEEKS'!K24<>"",'BYE WEEKS'!K24,"")

'PICK THEM WEEK 17  L10:L15
=IF('BYE WEEKS'!N19<>"",'BYE WEEKS'!N19,"")
=IF('BYE WEEKS'!N20<>"",'BYE WEEKS'!N20,"")
=IF('BYE WEEKS'!N21<>"",'BYE WEEKS'!N21,"")
=IF('BYE WEEKS'!N22<>"",'BYE WEEKS'!N22,"")
=IF('BYE WEEKS'!N23<>"",'BYE WEEKS'!N23,"")
=IF('BYE WEEKS'!N24<>"",'BYE WEEKS'!N24,"")

'PICK THEM WEEK 18  L10:L15
=IF('BYE WEEKS'!Q19<>"",'BYE WEEKS'!Q19,"")
=IF('BYE WEEKS'!Q20<>"",'BYE WEEKS'!Q20,"")
=IF('BYE WEEKS'!Q21<>"",'BYE WEEKS'!Q21,"")
=IF('BYE WEEKS'!Q22<>"",'BYE WEEKS'!Q22,"")
=IF('BYE WEEKS'!Q23<>"",'BYE WEEKS'!Q23,"")
=IF('BYE WEEKS'!Q24<>"",'BYE WEEKS'!Q24,"")
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,681
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
please upload your file with Xl2BB addin.
How you pick Week number (which cell you have dropdown)?
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,681
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You want Formula within Cell or only Values?
Team names is in Pick Them Weeks Worksheet? Is this correct?
 

TLS49

Board Regular
Joined
Nov 26, 2019
Messages
98
Office Version
  1. 365
Platform
  1. Windows
There a link on top my post. to download my workbook. all formulas is on all 18 pick them weeks. I put all section

1. ROAD TEAMS NAMES C3:C18
2. HOME TEAMS NAMES G3:G18
3. PICK THEM WEEK 1 to 18 L10:L15 where Bye Teams
 
Last edited:

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,681
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
If you want formula at Cells Try this:
VBA Code:
Sub AddFormula()
Dim sh As Worksheet, i As Long, K As Long, L As Long
For i = 7 To 39 Step 2
K = Int((i - 6) / 12) * 8 + 3
L = ((i - 6) Mod 12) * 2 - ((i - 7) Mod 12) / 2
'Debug.Print K & ", " & L & ", " & Sheets(i).Name & ", " & Sheets(i).Index
Sheets(i).Range("C3:C18").Formula = "=IF('" & Sheets(i - 1).Name & "'!D2<>"""",'" & Sheets(i - 1).Name & "'!D2,""BYE WEEK TEAM"")"
Sheets(i).Range("G3:G18").Formula = "=IF('" & Sheets(i - 1).Name & "'!G2<>"""",'" & Sheets(i - 1).Name & "'!G2,""BYE WEEK TEAM"")"
Sheets(i).Range("L10:L15").Formula = "=IF('" & Sheets("BYE WEEKS").Name & "'!" & Cells(K, L).Address(0, 0) & "<>"""",'" & Sheets("BYE WEEKS").Name & "'!" & Cells(K, L).Address(0, 0) & ","""")"
Next i
Sheets(46).Range("C3:C18").Formula = "=IF('" & Sheets(45).Name & "'!D2<>"""",'" & Sheets(45).Name & "'!D2,""BYE WEEK TEAM"")"
Sheets(46).Range("G3:G18").Formula = "=IF('" & Sheets(45).Name & "'!G2<>"""",'" & Sheets(45).Name & "'!G2,""BYE WEEK TEAM"")"
Sheets(46).Range("L10:L15").Formula = "=IF('" & Sheets("BYE WEEKS").Name & "'!Q19<>"""",'" & Sheets("BYE WEEKS").Name & "'!Q19,"""")"
End Sub

if you want only Results try this:
VBA Code:
Sub AddFormula()
Dim sh As Worksheet, i As Long, K As Long, L As Long
For i = 7 To 39 Step 2
K = Int((i - 6) / 12) * 8 + 3
L = ((i - 6) Mod 12) * 2 - ((i - 7) Mod 12) / 2
'Debug.Print K & ", " & L & ", " & Sheets(i).Name & ", " & Sheets(i).Index
Sheets(i).Range("C3:C18").Formula = "=IF('" & Sheets(i - 1).Name & "'!D2<>"""",'" & Sheets(i - 1).Name & "'!D2,""BYE WEEK TEAM"")"
Sheets(i).Range("G3:G18").Formula = "=IF('" & Sheets(i - 1).Name & "'!G2<>"""",'" & Sheets(i - 1).Name & "'!G2,""BYE WEEK TEAM"")"
Sheets(i).Range("L10:L15").Formula = "=IF('" & Sheets("BYE WEEKS").Name & "'!" & Cells(K, L).Address(0, 0) & "<>"""",'" & Sheets("BYE WEEKS").Name & "'!" & Cells(K, L).Address(0, 0) & ","""")"
Sheets(i).Range("C3:C18").Value = Sheets(i).Range("C3:C18").Value
Sheets(i).Range("G3:G18").Value = Sheets(i).Range("G3:G18").Value
Sheets(i).Range("L10:L15").Value = Sheets(i).Range("L10:L15").Value
Next i
Sheets(46).Range("C3:C18").Formula = "=IF('" & Sheets(45).Name & "'!D2<>"""",'" & Sheets(45).Name & "'!D2,""BYE WEEK TEAM"")"
Sheets(46).Range("G3:G18").Formula = "=IF('" & Sheets(45).Name & "'!G2<>"""",'" & Sheets(45).Name & "'!G2,""BYE WEEK TEAM"")"
Sheets(46).Range("L10:L15").Formula = "=IF('" & Sheets("BYE WEEKS").Name & "'!Q19<>"""",'" & Sheets("BYE WEEKS").Name & "'!Q19,"""")"
Sheets(46).Range("C3:C18").Value = Sheets(46).Range("C3:C18").Value
Sheets(46).Range("G3:G18").Value = Sheets(46).Range("G3:G18").Value
Sheets(46).Range("L10:L15").Value = Sheets(46).Range("L10:L15").Value
End Sub
 

Forum statistics

Threads
1,148,176
Messages
5,745,191
Members
423,931
Latest member
thangvan114

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top