PDA

View Full Version : Solved: Insert Formula by VBA method



Ann_BBO
11-06-2009, 01:40 AM
Hi All,

I had already built in the formula in the ColumnC before. Now, I would like to insert the formula by VBA method. The formula format is depended on the cell whether is merged.

Pattern:
(i). C2 is single cell
C2 =IF(BinStr2HexStr(CONCATENATE(RC[6]))=RC[-1],"",BinStr2HexStr(CONCATENATE(RC[6])))

(ii). C3 is merged with C4. (i.e. 2 cells are merged)
C3 =IF(BinStr2HexStr(CONCATENATE(R[1]C[6],RC[6]))=RC[-1],"",BinStr2HexStr(CONCATENATE(R[1]C[6],RC[6])))

(iii). C5 is merged with C6,C7. (i.e. 3 cells are merged)
C5 =IF(BinStr2HexStr(CONCATENATE(R[2]C[6],R[1]C[6],RC[6]))=RC[-1],"",BinStr2HexStr(CONCATENATE(R[2]C[6],R[1]C[6],RC[6])))

… upto n cells are merged.

Note BinStr2HexStr is my self-defined function.

Thanks,
Ann

lucas
11-06-2009, 09:51 AM
merged cells will make this very difficult. Are you trying to put the formula in column 9 or your column header "Bit Pattern"?

p45cal
11-06-2009, 10:46 AM
Try:
Sub Formula()
i = 2 'row no.
With Sheets("Sheet1")
Do
Select Case .Cells(i, 3).MergeArea.Cells.Count
Case 1: .Cells(i, 3).FormulaR1C1 = "=IF(BinStr2HexStr(CONCATENATE(RC[6]))=RC[-1],"""",BinStr2HexStr(CONCATENATE(RC[6])))"
Case 2: .Cells(i, 3).FormulaR1C1 = "=IF(BinStr2HexStr(CONCATENATE(R[1]C[6],RC[6]))=RC[-1],"""",BinStr2HexStr(CONCATENATE(R[1]C[6],RC[6])))"
Case 3: .Cells(i, 3).FormulaR1C1 = "=IF(BinStr2HexStr(CONCATENATE(R[2]C[6],R[1]C[6],RC[6]))=RC[-1],"""",BinStr2HexStr(CONCATENATE(R[2]C[6],R[1]C[6],RC[6])))"
End Select
i = i + .Cells(i, 3).MergeArea.Cells.Count
Loop Until .Cells(i, 4) = ""
End With
End Sub

Ann_BBO
11-07-2009, 12:35 AM
Hi All,

Thanks for lucas and p45cal. p45cal, you are right. Your code is suitable to me. But, if i have the case 1,2,3,4,5....n (i.e merge area count), do i need to create total n case in the code? Have any other method to improve it. Just for interest. Thanks.

Thanks,
Ann

p45cal
11-07-2009, 02:57 AM
Well, it's a balancing act. What's the max value of n? If it's, say, 7, then yes, the most practical way is to add a few more Case statements.

If it's much more than that then you could work out a way to construct the formulae in code and place them in the sheet.. but it'd be convoluted.

The next question I'd ask is whether the formulae have to be in the sheet at all? Would just seeing the results there do instead? It would be significantly easier to write code to calculate the results and place them in the cells.

p45cal
11-07-2009, 10:31 AM
It would be significantly easier to write code to calculate the results and place them in the cells.
You could try:Sub Formula3()
Dim BinStr As String
i = 2 'row no.
With Sheets("Sheet1")
Do
Set RngHexStr = .Cells(i, 3).Offset(, 6).Resize(.Cells(i, 3).MergeArea.Cells.Count)
BinStr = ""
For j = 1 To RngHexStr.Cells.Count: BinStr = RngHexStr(j) & BinStr: Next j
HexStr = BinStr2HexStr(BinStr)
If UCase(.Cells(i, 2).Value) = UCase(HexStr) Then .Cells(i, 3) = Empty Else .Cells(i, 3) = HexStr
i = i + .Cells(i, 3).MergeArea.Cells.Count
Loop Until .Cells(i, 4) = ""
End With
End Sub
(the code above hasn't been indented properly by thecodenet.com because it doesn't cope with For and Next being on the same line - the code is still valid)