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

You can do this with a formula

=(C2*1000000)+(B2*10000)+5000


or
=Value(C2&B2&5000)


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=198747

http://www.thecodecage.com/forumz

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

I didn't know your wanted a macro solution. Here if a very simple
macro

LastRow = Range("A" & Rows.count).end(xlup).row
Range("B2").formula = "=(C2*1000000)+(B2*10000)+5000"
Range("B2").copy _
Destination:=Range("B2:B" & LastRow)


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=198747

http://www.thecodecage.com/forumz