Breaking of range code into multiple lines and turn formulas of selection to values

R

ron2k

Board Regular
Joined
Jan 28, 2008
Messages
139
Hi

I have a piece of code. I've been able to figure out what to do but I need some housecleaning tips:

With this code:
Code: 
Worksheets(Xlfsheet).Range("C7,C9:C10,C13:C14,C17:C24,C27,C29,C31:C35").Offset(0, colnum).Select
        Selection.FormulaR1C1 = "=VLOOKUP(RC[" & Var1 & "],IMPORT!C[" & Var2 & "]:C[" & Var3 & "],3,FALSE)"
        Selection.Value = Selection.Value

How can break this range "C7,C9:C10,C13:C14,C17:C24,C27,C29,C31:C35", into, say, three lines (my actual range goes all the way to row 835, but with few tips I'll be able to handle the entire range)

And then why is it what when turning the selection to values only. It pastes the values of C7 only? How can I make it paste the values of its related to its related cell? Meaning, right now the entire range above is filled with the value of C7 only, and want the value of C9:C10 into C9:C10, C13:C14 and so forth and so forth.

Thanks,

Ron
 

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).
Sort by date Sort by votes
Hi crew,

I made some progress with this code. It works great. The insertion of formulas is really fast, however turning those formulas to values is REALLY REALLY Slow. The formulas are entered in like two seconds, the values part doesn't get done till at least 2 minutes later (and I think I'm conservative here...)

Code: 
Private Sub UpdateFile111()
    On Error Resume Next
    Sheets(Xlfsheet).Select
    Worksheets(Xlfsheet).Range("C3") _
        .Offset(0, colnum).FormulaR1C1 = "ACTUAL"
    Set UpdFill1 = Worksheets(Xlfsheet).Range("C7,C9:C10,C13:C14,C17:C24,C27,C29,C31:C35").Offset(0, colnum)
    Set UpdFill2 = Worksheets(Xlfsheet).Range("C38:C42,C45:C46,C49,C51:C55,C58:C71").Offset(0, colnum)
    Set UpdFill3 = Worksheets(Xlfsheet).Range("C77:C107,C110:C111,C114,C116:C119,C122:C123").Offset(0, colnum)
    Set UpdFill4 = Worksheets(Xlfsheet).Range("C131:C141,C144:C145,C148:C149,C152:C160").Offset(0, colnum)
    Set UpdFill5 = Worksheets(Xlfsheet).Range("C168,C170:C172,C175:C178,C181,C183:C185,C192:C193").Offset(0, colnum)
    Set UpdFill6 = Worksheets(Xlfsheet).Range("C200:C213,C216,C218,C220,C222:C233,C236:C242,C245").Offset(0, colnum)
    Set UpdFill7 = Worksheets(Xlfsheet).Range("C247:C252,C255:C256,C259:C264,C267,C269,C271:C272").Offset(0, colnum)
    Set UpdFill8 = Worksheets(Xlfsheet).Range("C275:C277,C280:C284,C287:C291,C294,C296,C298").Offset(0, colnum)
    Set UpdFill9 = Worksheets(Xlfsheet).Range("C303:C351,C354:C496,C502:C505,C508:C523,C529:C530").Offset(0, colnum)
    Set UpdFill10 = Worksheets(Xlfsheet).Range("C553,C555,C557,C559,C561,C563,C565,C569:C575,C578:C587,C590,C592").Offset(0, colnum)
    Set UpdFill11 = Worksheets(Xlfsheet).Range("C594:C596,C599:C600,C603:C609,C612,C616:C620,C623:C626,C629").Offset(0, colnum)
    Set UpdFill12 = Worksheets(Xlfsheet).Range("C631:C632,C635:C638,C641:C642,C645,C647,C649,C651:C672").Offset(0, colnum)
    Set UpdFill13 = Worksheets(Xlfsheet).Range("C675:C679,C682:C685,C688,C690:C720,C725:C726").Offset(0, colnum)
    Set IntbrchI = Worksheets(Xlfsheet).Range("C126").Offset(0, colnum)
    Set IntbrchE = Worksheets(Xlfsheet).Range("C166").Offset(0, colnum)
    Set UpdFAll1 = Application.Union(UpdFill1, UpdFill2, UpdFill3, UpdFill4, UpdFill5, UpdFill6, UpdFill7, UpdFill8, UpdFill9, UpdFill10, UpdFill11, UpdFill12, UpdFill13)
    Set UpdFAll2 = Application.Union(UpdFAll1, IntbrchI, IntbrchE)
    With UpdFAll1
        .FormulaR1C1 = "=VLOOKUP(RC[" & Var1 & "],IMPORT!C[" & Var2 & "]:C[" & Var3 & "],3,FALSE)"
    End With
    With IntbrchI
        .FormulaR1C1 = "=IF(VLOOKUP(RC[" & Var1 & "],IMPORT!C[" & Var2 & "]:C[" & Var3 & "],3,FALSE)<0,(VLOOKUP(RC[" & Var1 & "],IMPORT!C[" & Var2 & "]:C[" & Var3 & "],3,FALSE)),0)"
    End With
    With IntbrchE
        .FormulaR1C1 = "=IF(VLOOKUP(RC[" & Var1 & "],IMPORT!C[" & Var2 & "]:C[" & Var3 & "],3,FALSE)>0,(VLOOKUP(RC[" & Var1 & "],IMPORT!C[" & Var2 & "]:C[" & Var3 & "],3,FALSE)),0)"
    End With
    For Each cc In UpdFAll2
            cc.Formula = cc.Value
    Next
    
    Application.CutCopyMode = False
End Sub

The code loops through 15 worksheets, so it takes more than 1/2 hour to complete. Any takes on how to speed up the value conversion part.

Thanks,

Ron
 
Upvote 0
You must log in or register to reply here.

Similar threads

VinceF
  • Question
Circular Reference Error
Replies
8
Views
399
VinceF
VinceF
N
  • Question
Count - specific consecutive order
Replies
6
Views
523
Peter_SSs
Peter_SSs
R
  • Solved
Having trouble with Average Formula
Replies
6
Views
294
Fluff
Fluff
V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
205
etaf
etaf
K
  • Question
Auto Sort (Descinding) based on a column header using VBA
Replies
1
Views
97
Fluff
Fluff

Forum statistics

Threads
1,215,139
Messages
6,123,259
Members
449,093
Latest member
Vincent Khandagale

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