Looping and Formulas Disappearing

imback2nite

imback2nite

Board Regular
Joined
Oct 30, 2004
Messages
203
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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
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
bstory84 said:
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
Click to expand...
Fantastic! That worked Great! Once again, Thank you!
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Rotating Data from Horizontal to Vertical using VBA Code
Replies
3
Views
134
kevin9999
K
I
  • Question
VBA to remove formulas with values causing wrong calculations
2
Replies
12
Views
254
Peter_SSs
Peter_SSs
J
  • Solved
line graphs
Replies
5
Views
170
awoohaw
awoohaw
J
  • Solved
help finding my error
Replies
1
Views
131
Proulxs
P
D
  • Question
"Disabling" calculation of certain formulas until source cells updated -- is there a better way to do this?
Replies
0
Views
447
d0rian
D

Forum statistics

Threads
1,214,583
Messages
6,120,383
Members
448,956
Latest member
JPav

Share this page

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