Solved: Insert Formula by VBA method

[Ann_BBO]

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]

merged cells will make this very difficult. Are you trying to put the formula in column 9 or your column header "Bit Pattern"?

[p45cal]

Try:
[vba]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

[/vba]

[Ann_BBO]

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]

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]

It would be significantly easier to write code to calculate the results and place them in the cells.

You could try:[vba]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
[/vba](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)