Looping and Formulas Disappearing

imback2nite

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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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

M
  • Question Question
My formula is unnecessarily copied to the adjacent cell when entering data into another cell
Replies
6
Views
201
MrsGrayMarlin
M
D
  • Question Question
VBA to Export Tab to .CSV with date
Replies
1
Views
211
abdelfattah
A
J
  • Locked
  • Question Question
LET/VSTACK/FILTER question
Replies
6
Views
358
Fluff
Fluff
J
  • Solved
Assistance with Index Match formula
Replies
2
Views
181
Jay88
J
R
  • Question Question
[VBA] - Need to Hide one column and need sum of columns
Replies
0
Views
182
rsolanki
R

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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