Macro to extract the data in a certain cell
 

Hi Guys,

I'm trying to come up with a macro program to get the Sequence no. (in
bold font) and write it in column C. In Colum A I have the data below,
In Column B I have the Incident number and In column C i wish to write
the extracted nubmers from Column A. I was thinking that the word
Sequence no. can be used as a search item so that excel will know that
the number below it is the number that needs to be written on column
C.

I hope you can respond to this i have been working for hours to get
this running but I haven't really got any success so far.

Thanks in advance.


Description
Vendor/Supplier:XXXXXXXXX
Invoice no.
Sequence no.
10024350
10024370
Vendor/Supplier: XXXXXXXXXX
Invoice no.
SKU/PLU No. 8888010101618
8888010101601
Sequence No.
13016989

Vendor/Supplier: XXXXXXXXXX
Invoice No.
Sequence no.
3036940
Product code No. 1256453119465
Qty: 24
Vendor/Supplier: XXXXXXXXXX
Credit note. CN126140
Sequence no.
25022191

Vendor/Supplier: Diethelm Singapore
Credit note. 1950201830
Sequence no.
13016989


--
Eladamri
------------------------------------------------------------------------
Eladamri's Profile: http://www.excelforum.com/member.php...o&userid=35099
View this thread: http://www.excelforum.com/showthread...hreadid=559205

Macro to extract the data in a certain cell
 

Hi Guys

I really hope you can help me with this one.
:confused

--
Eladamr
-----------------------------------------------------------------------
Eladamri's Profile: http://www.excelforum.com/member.php...fo&userid=3509
View this thread: http://www.excelforum.com/showthread.php?threadid=55920

Macro to extract the data in a certain cell
 

Assuming each record is in a single cell in column A and each is separated
by vbLf, this may work<no error handling and not tested:

Public function GetSeq(argRange as range) as string
Dim Data as variant
dim i as long
data=split(argrange.value,vblf)
for i=1 to ubound(data)
if lcase(data(i))="sequence no." then
getseq=data(i+1)
exit function
end if
next
getseq="No Seq"
End function

NickHK

"Eladamri" wrote in
message ...

Hi Guys,

I'm trying to come up with a macro program to get the Sequence no. (in
bold font) and write it in column C. In Colum A I have the data below,
In Column B I have the Incident number and In column C i wish to write
the extracted nubmers from Column A. I was thinking that the word
Sequence no. can be used as a search item so that excel will know that
the number below it is the number that needs to be written on column
C.

I hope you can respond to this i have been working for hours to get
this running but I haven't really got any success so far.

Thanks in advance.


Description
Vendor/Supplier:XXXXXXXXX
Invoice no.
Sequence no.
10024350
10024370
Vendor/Supplier: XXXXXXXXXX
Invoice no.
SKU/PLU No. 8888010101618
8888010101601
Sequence No.
13016989

Vendor/Supplier: XXXXXXXXXX
Invoice No.
Sequence no.
3036940
Product code No. 1256453119465
Qty: 24
Vendor/Supplier: XXXXXXXXXX
Credit note. CN126140
Sequence no.
25022191

Vendor/Supplier: Diethelm Singapore
Credit note. 1950201830
Sequence no.
13016989


--
Eladamri
------------------------------------------------------------------------
Eladamri's Profile:
http://www.excelforum.com/member.php...o&userid=35099
View this thread: http://www.excelforum.com/showthread...hreadid=559205

Macro to extract the data in a certain cell
 

Hi NickHK,

I still receive an error
Compile Error:
Expected End Sub

But thank you very much for your help. I Greatly appreciate your help

--
Eladamr
-----------------------------------------------------------------------
Eladamri's Profile: http://www.excelforum.com/member.php...fo&userid=3509
View this thread: http://www.excelforum.com/showthread.php?threadid=55920

Macro to extract the data in a certain cell
 

You can't get that error as it is a Function, not a Sub.
Look elsewhere in you code for a missing "End Sub".

NickHK

"Eladamri" wrote in
message ...

Hi NickHK,

I still receive an error
Compile Error:
Expected End Sub

But thank you very much for your help. I Greatly appreciate your help.


--
Eladamri
------------------------------------------------------------------------
Eladamri's Profile:
http://www.excelforum.com/member.php...o&userid=35099
View this thread: http://www.excelforum.com/showthread...hreadid=559205

Macro to extract the data in a certain cell
 

"Eladamri" wrote in
message ...

Hi Guys,

I'm trying to come up with a macro program to get the Sequence no. (in
bold font) and write it in column C. In Colum A I have the data below,
In Column B I have the Incident number and In column C i wish to write
the extracted nubmers from Column A. I was thinking that the word
Sequence no. can be used as a search item so that excel will know that
the number below it is the number that needs to be written on column
C.

I hope you can respond to this i have been working for hours to get
this running but I haven't really got any success so far.

Thanks in advance.


Description
Vendor/Supplier:XXXXXXXXX
Invoice no.
Sequence no.
10024350
10024370
Vendor/Supplier: XXXXXXXXXX
Invoice no.
SKU/PLU No. 8888010101618
8888010101601
Sequence No.
13016989

Maybe something like this:

Sub Sequence()
Dim SequenceFollows As Boolean

For Each cell In Selection
If SequenceFollows = True And IsNumeric(cell.Offset(0, -2)) Then
cell.Value = cell.Offset(0, -2).Value
Else
SequenceFollows = False
End If
If LCase(cell.Offset(0, -2)) = "sequence no." Then SequenceFollows =
True
Next cell
End Sub

The macro assumes that you have selected cells in column C. It then searches
all cells 2 columns to the left (column A), sets an internal flag if it
finds the word "Sequence No. (you should be consistent in your spelling -
you changed capitalization in your example) and then extracts the following
numbers up to the next non-numeric value.

Good luck.

Joerg