Prev: Copy image in excel to publisher document using vba
Next: Filter by error
From: climate on 27 Apr 2010 05:58
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
From: joel on 27 Apr 2010 06:09

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/showthread.php?t=198747

http://www.thecodecage.com/forumz

From: SteAXA on 27 Apr 2010 06:47
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
From: climate on 28 Apr 2010 02:55
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
From: joel on 28 Apr 2010 05:53

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/showthread.php?t=198747

http://www.thecodecage.com/forumz

 | 
Pages: 1
Prev: Copy image in excel to publisher document using vba
Next: Filter by error