Macro to extract the data in a certain cell

  • Thread starter Thread starter Eladamri
  • Start date Start date
E

Eladamri

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
 
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
 
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
 
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 said:
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
Click to expand...

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
 
Back
Top