Dear Experts
My data on sheet1 is classified to many groups, any group has special values
on column C and column B. also, any group seprate with 5 blank row than other.
I need to a macro, when run it, write a code under any group at column H
based on numbers or special value of column C and columb B and then add 5000
to it.
Example for first group: special values on column C is 1 and special value
on column B is 10. expected code under column H for this group will be
(1105000).

any help will be appreciating
regards

if i understand right, it's can solve your problem (in excel 2003):

Sub Macro1()
Dim bStop As Boolean
'i start from the cell b1
Range("B1").Select
bStop = False
While Not bStop
'i go down until i find an empty cell
Selection.End(xlDown).Select
'i change the active cell and i move to H cell (independently from
the row)
ActiveCell.Offset(1, 6).Select
'i write my formula text
ActiveCell.FormulaR1C1 =
"=CONCATENATE(R[-1]C[-5],R[-1]C[-6],""5000"")"
'i return on B cell (because i don't know if H cells are compiled)
ActiveCell.Offset(0, -6).Select
'i go down until i find a new compiled cell
Selection.End(xlDown).Select
'if the last cell of the sheet i must stop the routine
If ActiveCell.Value = "" Then
bStop = True
End If
Wend
'i return on the last compiled cell
Selection.End(xlUp).Select
End Sub

Bye

Hi
steAXA, thank you very much , your code is very nice.
joel, thank's for your help , your formula need to copy and paste.

best regards

"SteAXA" wrote:

> if i understand right, it's can solve your problem (in excel 2003):
>
> Sub Macro1()
> Dim bStop As Boolean
> 'i start from the cell b1
> Range("B1").Select
> bStop = False
> While Not bStop
> 'i go down until i find an empty cell
> Selection.End(xlDown).Select
> 'i change the active cell and i move to H cell (independently from
> the row)
> ActiveCell.Offset(1, 6).Select
> 'i write my formula text
> ActiveCell.FormulaR1C1 =
> "=CONCATENATE(R[-1]C[-5],R[-1]C[-6],""5000"")"
> 'i return on B cell (because i don't know if H cells are compiled)
> ActiveCell.Offset(0, -6).Select
> 'i go down until i find a new compiled cell
> Selection.End(xlDown).Select
> 'if the last cell of the sheet i must stop the routine
> If ActiveCell.Value = "" Then
> bStop = True
> End If
> Wend
> 'i return on the last compiled cell
> Selection.End(xlUp).Select
> End Sub
>
> Bye