Need to Use Excel Worksheet Formulas and Formatting into Values using VBA..

[all4excel]

Need to Use Excel Worksheet Formulas and Formatting into Values using VBA..


Dear Board,

I have finalized particular formatting and specific formulas for each different column..
Now I need to use the same Formatting Information and also use the same Formulas however in the VBA. approach.

Example:
The Matrix Range where I need to use the Formatting and Formulas starts from the Column A10 till Column K unlimited depending on certain criteria..

I have formulas for each column i.e Cells A10 till K10..
Now the formula in the Cell A10 should be copied till a certain Last Row which I can derive, however I need to use it in such a way that the entire Column A10:A110 ( assuming 110 is the last row till which i need the formula to be filled)

Then how do I put the formatting and then the formula and then convert all this into values..

This is the Formatting Information and also the Formula for the first cell i.e. A10 which needs to be there till the last row = 110.

.Address  =  $A$10
.NumberFormat = "General"
.Formula = "=IF(ROWS($A$10:$A10)<=PassCnt,ROWS($A$10:$A10),"""")"
.HorizontalAlignment = -4108
.VerticalAlignment = -4107
.IndentLevel = 0
.Orientation = -4128
.WrapText = False
.ColumnWidth = 3.64
.RowHeight = 15
.Font.Name = "Book Antiqua"
.Font.Size = 11
.Font.Bold = True
.Font.Italic = False
.Font.Underline = -4142
.Font.Color = 8388736
.Font.ColorIndex = 13
.Font.Superscript = False
.Font.Subscript = False
.Font.Strikethrough = False

Now this was a very simple and compact formula however, in the next cell i.e. B10 there is a mcuh bigger formula which has an INDIRECT function as well as it is an ARRAY formula.

"=IF(OR($A10="",ISBLANK(INDEX(INDIRECT("'"&$D$2&"'!$A$2:$Z$"&LastRow1),SMALL(IF((INDIRECT("'"&$D$2&"'!$E$2:$E$"&LastRow1)>=$C$5)*(INDIRECT("'"&$D$2&"'!$E$2:$E$"&LastRow1)<=$C$6),ROW(INDIRECT("'"&$D$2&"'!$E$2:$E$"&LastRow1))-ROW(INDIRECT("'"&$D$2&"'!$A$2"))+1),ROWS(B$10:B10)),MATCH(B$9,INDIRECT("'"&$D$2&"'!$1:$1"),0)))),"",INDEX(INDIRECT("'"&$D$2&"'!$A$2:$Z$"&LastRow1),SMALL(IF((INDIRECT("'"&$D$2&"'!$E$2:$E$"&LastRow1)>=$C$5)*(INDIRECT("'"&$D$2&"'!$E$2:$E$"&LastRow1)<=$C$6),ROW(INDIRECT("'"&$D$2&"'!$E$2:$E$"&LastRow1))-ROW(INDIRECT("'"&$D$2&"'!$A$2"))+1),ROWS(B$10:B10)),MATCH(B$9,INDIRECT("'"&$D$2&"'!$1:$1"),0)))"

.Address  =  $B$10
.Value = CHQ
.NumberFormat = "General"
.HorizontalAlignment = -4108
.VerticalAlignment = -4108
.IndentLevel = 0
.Orientation = -4128
.WrapText = False
.ColumnWidth = 7.55
.RowHeight = 15
.Font.Name = "Book Antiqua"
.Font.Size = 11
.Font.Bold = True
.Font.Italic = False
.Font.Underline = -4142
.Font.Color = 6299648
.Font.ColorIndex = 49
.Font.Superscript = False
.Font.Subscript = False
.Font.Strikethrough = False

Now the problem in using the formulas is of the double quotes and also of the single quotes.
So, I was trying to use the Substitute Function to convert the regular worksheet formulas into VBA type formulas by replacing each pair of double quote with double pair.

Please someone guide me on how do I use the same..efficiently where I can use the same formulas and the formatting but in a better manner using VBA.

Thanks in advance.

Regards
all4excel

 

[all4excel]

My idea is to use a particular drop-down which would impact the entire sheet as in the Range of the Rows from A10 till A?

I think Worksheet_Change event would be appropriate?

To make changes from A1- till K??

For each Column I have different Formulas but I need these formulas to be first used as formulas and then paste them as values.

In the regular approach without VBA what I am doing is I am copying the formulas to a very large Range since the data is not fixed length and not really sure how much more or less would be required?

Maybe if the VBA approach can be employed it will just run the formula and formatting till the exact number of rows and make the file lighter as keeping these extra rows of array formulas is rendering it very slow..

Thanks in advance.

 

[all4excel]

Dear Excel Experts,

I need your advice on this as i have been googling to get help on this particular query, I came across a wonderful link regarding using the formulas from Worksheet in the VBA Module and i tried them but without any result..

http://colinlegg.wordpress.com/2012/05/23/working-with-range-formulaarray-in-vba/

I tried the below mentioned option..

Sub Option2()

Sheet1.Range("B10").FormulaArray = _
"=IF(OR($A10="""",ISBLANK(INDEX(AcctRng,SMALL(IF((DtColRng>=$C$5)*(DtColRng<=$C$6),ROW(DtColRng)-ROW(RefrncCell)+1),ROWS(B$10:B10)),MATCH(B$9,RefrncRow,0)))),"""",INDEX(AcctRng,SMALL(IF((DtColRng>=$C$5)*(DtColRng<=$C$6),ROW(DtColRng)-ROW(RefrncCell)+1),ROWS(B$10:B10)),MATCH(B$9,RefrncRow,0)))"

Sheet1.Range("B10:B"&LastRow).FillDown

End Sub

where LastRow is a String variable which holds the Last Row value

The above is an array formula which needs to be present in the cells from B10till B? and after copying it as formulas it needs to be immediately converted to values.

Thanks in advance.

all4excel

 

[all4excel]

After a lot of Trial and Error I came across the information that the length of the formula should be below 255 characters which I think in my case is far more than that, so how do I combat this problem?

 

[all4excel]

Dear Experts,

Please I need some helping hand..

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Long
LastRow = Range("E1").Value + 9


Dim wrksht As Worksheet


Set wrksht = Worksheets("PB")


Const ColB = "=IF(OR($A10="""",ISBLANK(INDEX(AcctRng,SMALL(IF((DtColRng>=$C$5)*(DtColRng<=$C$6),ROW(DtColRng)-ROW(RefrncCell)+1),ROWS(B$10:B10)),MATCH(B$9,RefrncRow,0)))),"""",INDEX(AcctRng,SMALL(IF((DtColRng>=$C$5)*(DtColRng<=$C$6),ROW(DtColRng)-ROW(RefrncCell)+1),ROWS(B$10:B10)),MATCH(B$9,RefrncRow,0)))"






If Target.Address(0, 0) = "C5" Then
    If Not IsEmpty(Target) Then   
    
        Sheets("PB").Range(Cells(10, 1), Cells(LastRow, 11)).ClearContents
        
        With wrksht
        
            With .Range(.Cells(10, 1), .Cells(LastRow, 1))
                .Formula = WorksheetFunction.Substitute("=IF(ROWS($A$10:$A10)<=PassCnt,ROWS($A$10:$A10),"")", """", """""")                
                .Value = .Value
            End With       
        
        
            With .Range(.Cells(10, 2), .Cells(LastRow, 2))
                .FormulaArray = ColB
                '.FormulaArray = .FormulaR1C1
            End With           
            
            
        End With
        
    End If
End If
End Sub

I have not done anything regarding FOrmatting yet and even the Array Formula for the column B does not work at all..

Also I need to Blank the Borders for the Range always..so is there something similar to

Sheets("PB").Range(Cells(10, 1), Cells(LastRow, 11)).ClearContents

Sheets("PB").Range(Cells(10, 1), Cells(LastRow, 11)).ClearBorder

Thanks in Advance..

 

[all4excel]

Modifying the earlier formula but still not working...

Tried with this style too in vain..

        Range("B10").FormulaArray = "=IF(OR($A10="""",Form1),"""",Form2)"        Range("B10:B" & LastRow).FillDown

        With wrksht
        
            With .Range(.Cells(10, 1), .Cells(LastRow, 1))
                .Formula = WorksheetFunction.Substitute("=IF(ROWS($A$10:$A10)<=$E$1,ROWS($A$10:$A10),"")", """", """""")
                .Value = .Value
                '.FormulaArray = .FormulaR1C1
            End With
        
        
        
            With .Range(.Cells(10, 2), .Cells(LastRow, 2))
                .FormulaArray = "=IF(OR($A10="""",Form1),"""",Form2)"
                .FormulaArray = .FormulaR1C1
            End With
            
            
            
        End With

I am still getting an error in the line .FormulaArray = .FormulaR1C1 for the Range b10:b?? and when I changed to .FormulaR10C2 it throws an error..

Please please I m just trying but need some explanations..

 

[all4excel]

Can someone please please help me I am not able to proceed further and the other thing that i realised is that I almost elven columns which would have such bigg ArraY formulas so do I need to break them into smaller formulas and then use it for VBA..

http://www.mrexcel.com/forum/showthread.php?434519-EXCEL-VBA-Using-long-Array-formula

 

[all4excel]

I dont believe this and I still dont know the reason how its working, my Formulas was a Long Formula which was reduced using 2 defined names..
Now when I was using .FormulaArray as mentioned in the several sites with the shortened formula to go with..

It gave me the same answer for all the cells in the same column then just to try something else I removed the Array from the .FormulaArray and it gave me the desired result..

So whats the reason behind that and now when I am using an Array formula normally to get the same result, how is the .Formula giving the same?

Please please tell me something experts, what do I need to do to get an answer on this?

all4excel

 

[all4excel]

Is really funny that I am just answering to myself in all the posts for this thread..
Still need a lot of clarity into why the formula started working as I needed to use more WorkSheet Formulas into..

I have simple formulas which are containing a combination of SUMIF and OFFSET and now I wanted to use these formulae in more than one cell, but when I tired using them simply without a Defined Name it did not work at all and threw errors and therefore I had to resort to a Defined Name though the length of this formula is well withing the 256 character limit..

As I have several cells which have the same formula except that the SUMIF Criteria reference changes for each of these formulas I will have to create several Defined Names which is extremely time-consuming and frustrating so is there a way out?

Range("H3").Formula = WorksheetFunction.Substitute("=H_3", """", """""")

The Defined Name is H_3 as it is for the cell H3 and I need to have this from H3 till H12 and again the same style from I3 till I12..

Defined Name - H_3 = SUMIF(OFFSET('Sheet1'!$B$16,0,0,'Sheet1'!$L$1+13,1),'Sheet1'!$F3&"*",OFFSET('Sheet1'!$K$16,0,0,'Sheet1'!$L$1+13,1))

Regards
all4excel