Looping and Formulas Disappearing

imback2nite

Board Regular
Joined
Oct 30, 2004
Messages
211
Office Version
  1. 2003 or older
Platform
  1. Windows
Hello. I'm using this code and it works as intended but it's lengthy. The first part may have to stay but is there a way to loop the 'replacement' part of it? The whole thing looks too long for what I'm trying to do. As a matter of fact I would like to keep the formulae in there respective cells but all of a sudden my formulas are disappearing. I put in the formula and it disappears leaving the sum. The sum in correct but the next time I use it, well... Is there a reason for this? I've been reading about formatting issues and I've removed it all and it still remains a problem. I would really appreciate any help. Thank you.
VBA Code:
Private Sub CommandButton2_Click()
   ActiveSheet.Unprotect 
    Range("W5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$W$4,SUM(Emp1!$J$3),"""")"
    Range("X5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$X$4,SUM(Emp1!$J$4),"""")"
    Range("Y5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$Y$4,SUM(Emp1!$J$5),"""")"
    Range("Z5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$Z$4,SUM(Emp1!$J$6),"""")"
    Range("AA5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AA$4,SUM(Emp1!$J$7),"""")"
    Range("AB5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AB$4,SUM(Emp1!$J$8),"""")"
    Range("AC5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AC$4,SUM(Emp1!$J$9),"""")"
    Range("AD5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AD$4,SUM(Emp1!$R$3),"""")"
    Range("AE5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AE$4,SUM(Emp1!$R$4),"""")"
    Range("AF5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AF$4,SUM(Emp1!$R$5),"""")"
    Range("AG5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AG$4,SUM(Emp1!$R$6),"""")"
    Range("AH5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AH$4,SUM(Emp1!$R$7),"""")"
    Range("AI5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AI$4,SUM(Emp1!$R$8),"""")"
    Range("AJ5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AJ$4,SUM(Emp1!$R$9),"""")"
    Range("AK5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AK$4,SUM(Emp1!$AA$3),"""")"
    Range("AL5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AL$4,SUM(Emp1!$AA$4),"""")"
    Range("AM5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AM$4,SUM(Emp1!$AA$5),"""")"
    Range("AN5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AN$4,SUM(Emp1!$AA$6),"""")"
    Range("AO5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AO$4,SUM(Emp1!$AA$7),"""")"
    Range("AP5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AP$4,SUM(Emp1!$AA$8),"""")"
    Range("AQ5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AQ$4,SUM(Emp1!$AA$9),"""")"
    Range("AR5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AR$4,SUM(Emp1!$AH$3),"""")"
    Range("AS5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AS$4,SUM(Emp1!$AH$4),"""")"
    Range("AT5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AT$4,SUM(Emp1!$AH$5),"""")"
    Range("AU5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AU$4,SUM(Emp1!$AH$6),"""")"
    Range("AV5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AV$4,SUM(Emp1!$AH$7),"""")"
    Range("AW5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AW$4,SUM(Emp1!$AH$8),"""")"
    Range("AX5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AX$4,SUM(Emp1!$AH$9),"""")"
           Range("W5:AX5").AutoFill Destination:=Range("W5:AX34"), Type:=xlFillDefault
       Range("W6:AX6").Replace What:="Emp1", Replacement:="Emp2"
       Range("W7:AX7").Replace What:="Emp1", Replacement:="Emp3"
       Range("W8:AX8").Replace What:="Emp1", Replacement:="Emp4"
       Range("W9:AX9").Replace What:="Emp1", Replacement:="Emp5"
       Range("W10:AX10").Replace What:="Emp1", Replacement:="Emp6"
       Range("W11:AX11").Replace What:="Emp1", Replacement:="Emp7"
       Range("W12:AX12").Replace What:="Emp1", Replacement:="Emp8"
       Range("W13:AX13").Replace What:="Emp1", Replacement:="Emp9"
       Range("W14:AX14").Replace What:="Emp1", Replacement:="Emp10"
       Range("W15:AX15").Replace What:="Emp1", Replacement:="Emp11"
       Range("W16:AX16").Replace What:="Emp1", Replacement:="Emp12"
       Range("W17:AX17").Replace What:="Emp1", Replacement:="Emp13"
       Range("W18:AX18").Replace What:="Emp1", Replacement:="Emp14"
       Range("W19:AX19").Replace What:="Emp1", Replacement:="Emp15"
       Range("W20:AX20").Replace What:="Emp1", Replacement:="Emp16"
       Range("W21:AX21").Replace What:="Emp1", Replacement:="Emp17"
       Range("W22:AX22").Replace What:="Emp1", Replacement:="Emp18"
       Range("W23:AX23").Replace What:="Emp1", Replacement:="Emp19"
       Range("W24:AX24").Replace What:="Emp1", Replacement:="Emp20"
       Range("W25:AX25").Replace What:="Emp1", Replacement:="Emp21"
       Range("W26:AX26").Replace What:="Emp1", Replacement:="Emp22"
       Range("W27:AX27").Replace What:="Emp1", Replacement:="Emp23"
       Range("W28:AX28").Replace What:="Emp1", Replacement:="Emp24"
       Range("W29:AX29").Replace What:="Emp1", Replacement:="Emp25"
       Range("W30:AX30").Replace What:="Emp1", Replacement:="Emp26"
       Range("W31:AX31").Replace What:="Emp1", Replacement:="Emp27"
       Range("W32:AX32").Replace What:="Emp1", Replacement:="Emp28"
       Range("W33:AX33").Replace What:="Emp1", Replacement:="Emp29"
       Range("W34:AX34").Replace What:="Emp1", Replacement:="Emp30"
Range("$W$4:$AX$34").Copy
Range("$W$4").PasteSpecial Paste:=xlPasteValues
    Range("O51").Select
    Application.ScreenUpdating = True
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:= _
        True, AllowFormattingCells:=True, AllowFormattingColumns:=False, _
        AllowFormattingRows:=False, AllowInsertingColumns:=False, AllowInsertingRows _
        :=False, AllowInsertingHyperlinks:=False, AllowDeletingColumns:=False, _
        AllowDeletingRows:=False, AllowSorting:=False, AllowFiltering:=False, _
        AllowUsingPivotTables:=False
    ActiveSheet.EnableSelection = xlNoRestrictions
    Application.ScreenUpdating = True
    Unload Me
End Sub
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
These 2 lines are removing the formulas and keeping the values. These are the only lines in your posted code that are not indented. So maybe they were added after?

VBA Code:
Range("$W$4:$AX$34").Copy
Range("$W$4").PasteSpecial Paste:=xlPasteValues
 
Upvote 0
Well, I added those lines after the formulas disappeared. What I had were the formulas in their respective cells. I noticed that the sums were not changing when information was added to the worksheet. I checked and the formulas were gone but prior values remained. The above code is what I had to put in to make sure the formulas were still there. ?
 
Upvote 0
In this case, .xlPasteValues essentially removes the formula leaving the value of the formula behind
Probably need more information though.
Next time you run it, check and see if your formulas--> Calculation Options--> are set to "Automatic". If they are set to "Manual" that may keep them from Recalculating.

You could force a re-calculate before your paste values statement.
VBA Code:
Range("$W$4:$AX$34").Calculate
Range("$W$4:$AX$34").Copy
Range("$W$4").PasteSpecial Paste:=xlPasteValues
 
Upvote 0
This is the code I'm using. I inadvertently included the Copy and paste values. What I'm looking for is if there's a way to 'loop' the code so that it will run faster or, in my case, better.
VBA Code:
Private Sub CommandButton2_Click()
   ActiveSheet.Unprotect
    Range("W5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$W$4,SUM(Emp1!$J$3),"""")"
    Range("X5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$X$4,SUM(Emp1!$J$4),"""")"
    Range("Y5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$Y$4,SUM(Emp1!$J$5),"""")"
    Range("Z5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$Z$4,SUM(Emp1!$J$6),"""")"
    Range("AA5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AA$4,SUM(Emp1!$J$7),"""")"
    Range("AB5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AB$4,SUM(Emp1!$J$8),"""")"
    Range("AC5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AC$4,SUM(Emp1!$J$9),"""")"
    Range("AD5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AD$4,SUM(Emp1!$R$3),"""")"
    Range("AE5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AE$4,SUM(Emp1!$R$4),"""")"
    Range("AF5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AF$4,SUM(Emp1!$R$5),"""")"
    Range("AG5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AG$4,SUM(Emp1!$R$6),"""")"
    Range("AH5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AH$4,SUM(Emp1!$R$7),"""")"
    Range("AI5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AI$4,SUM(Emp1!$R$8),"""")"
    Range("AJ5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AJ$4,SUM(Emp1!$R$9),"""")"
    Range("AK5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AK$4,SUM(Emp1!$AA$3),"""")"
    Range("AL5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AL$4,SUM(Emp1!$AA$4),"""")"
    Range("AM5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AM$4,SUM(Emp1!$AA$5),"""")"
    Range("AN5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AN$4,SUM(Emp1!$AA$6),"""")"
    Range("AO5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AO$4,SUM(Emp1!$AA$7),"""")"
    Range("AP5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AP$4,SUM(Emp1!$AA$8),"""")"
    Range("AQ5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AQ$4,SUM(Emp1!$AA$9),"""")"
    Range("AR5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AR$4,SUM(Emp1!$AH$3),"""")"
    Range("AS5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AS$4,SUM(Emp1!$AH$4),"""")"
    Range("AT5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AT$4,SUM(Emp1!$AH$5),"""")"
    Range("AU5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AU$4,SUM(Emp1!$AH$6),"""")"
    Range("AV5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AV$4,SUM(Emp1!$AH$7),"""")"
    Range("AW5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AW$4,SUM(Emp1!$AH$8),"""")"
    Range("AX5").Formula = "=IF(VLOOKUP('Hours Graph'!$U$2,'Hours Graph'!$T$4:$U$31,2,FALSE)='Hours Graph'!$AX$4,SUM(Emp1!$AH$9),"""")"
           Range("W5:AX5").AutoFill Destination:=Range("W5:AX34"), Type:=xlFillDefault
       Range("W6:AX6").Replace What:="Emp1", Replacement:="Emp2"
       Range("W7:AX7").Replace What:="Emp1", Replacement:="Emp3"
       Range("W8:AX8").Replace What:="Emp1", Replacement:="Emp4"
       Range("W9:AX9").Replace What:="Emp1", Replacement:="Emp5"
       Range("W10:AX10").Replace What:="Emp1", Replacement:="Emp6"
       Range("W11:AX11").Replace What:="Emp1", Replacement:="Emp7"
       Range("W12:AX12").Replace What:="Emp1", Replacement:="Emp8"
       Range("W13:AX13").Replace What:="Emp1", Replacement:="Emp9"
       Range("W14:AX14").Replace What:="Emp1", Replacement:="Emp10"
       Range("W15:AX15").Replace What:="Emp1", Replacement:="Emp11"
       Range("W16:AX16").Replace What:="Emp1", Replacement:="Emp12"
       Range("W17:AX17").Replace What:="Emp1", Replacement:="Emp13"
       Range("W18:AX18").Replace What:="Emp1", Replacement:="Emp14"
       Range("W19:AX19").Replace What:="Emp1", Replacement:="Emp15"
       Range("W20:AX20").Replace What:="Emp1", Replacement:="Emp16"
       Range("W21:AX21").Replace What:="Emp1", Replacement:="Emp17"
       Range("W22:AX22").Replace What:="Emp1", Replacement:="Emp18"
       Range("W23:AX23").Replace What:="Emp1", Replacement:="Emp19"
       Range("W24:AX24").Replace What:="Emp1", Replacement:="Emp20"
       Range("W25:AX25").Replace What:="Emp1", Replacement:="Emp21"
       Range("W26:AX26").Replace What:="Emp1", Replacement:="Emp22"
       Range("W27:AX27").Replace What:="Emp1", Replacement:="Emp23"
       Range("W28:AX28").Replace What:="Emp1", Replacement:="Emp24"
       Range("W29:AX29").Replace What:="Emp1", Replacement:="Emp25"
       Range("W30:AX30").Replace What:="Emp1", Replacement:="Emp26"
       Range("W31:AX31").Replace What:="Emp1", Replacement:="Emp27"
       Range("W32:AX32").Replace What:="Emp1", Replacement:="Emp28"
       Range("W33:AX33").Replace What:="Emp1", Replacement:="Emp29"
       Range("W34:AX34").Replace What:="Emp1", Replacement:="Emp30"
    Range("O51").Select
    Application.ScreenUpdating = True
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:= _
        True, AllowFormattingCells:=True, AllowFormattingColumns:=False, _
        AllowFormattingRows:=False, AllowInsertingColumns:=False, AllowInsertingRows _
        :=False, AllowInsertingHyperlinks:=False, AllowDeletingColumns:=False, _
        AllowDeletingRows:=False, AllowSorting:=False, AllowFiltering:=False, _
        AllowUsingPivotTables:=False
    ActiveSheet.EnableSelection = xlNoRestrictions
    Application.ScreenUpdating = True
    Unload Me
End Sub
 
Upvote 0
Try this loop in place of your "replace" statements.

VBA Code:
Dim iRow As Integer
Dim iEmp As Integer
    iEmp = 2

    For iRow = 6 To 34 'Rows 6 thru 34
        Range("W" & iRow & ":AX" & iRow).Replace What:="Emp1", Replacement:="Emp" & iEmp
        iEmp = iEmp + 1
    Next iRow
 
Upvote 0
Try this loop in place of your "replace" statements.

VBA Code:
Dim iRow As Integer
Dim iEmp As Integer
    iEmp = 2

    For iRow = 6 To 34 'Rows 6 thru 34
        Range("W" & iRow & ":AX" & iRow).Replace What:="Emp1", Replacement:="Emp" & iEmp
        iEmp = iEmp + 1
    Next iRow
Fantastic! That worked Great! Once again, Thank you!
 
Upvote 0

Forum statistics

Threads
1,222,072
Messages
6,163,751
Members
451,855
Latest member
mcook36155

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
Back
Top