how to use vba to mk stickers.. 1

[ifthenelse]

example: (refer help10.xls)

http://www.geocities.com/remember_it/help10.xls

Option Explicit
In my job, I often have to printer stickers which show the info of products. However, I find the traditional way VERY INCONVENIENT and UNEFFICIENT.

Traditional Flow (of making stickers):
1) extract product info (the format of which is unsuitable for my company) from the details provided by the supplier
2) enter the extracted product info to my company's checklist sheet
eg. Sheets("checklist&quot
3) re-enter the product info into my company's sticker sheet
eg. Sheets("sticker&quot
--------------------------------------------------------------------------------------------------------------------------------
Sub Problem()
Since
Sheets(&quot;sticker&quot 's format <> Sheets(&quot;checklist&quot 's format
Therefore
&quot;paste all&quot; CANNOT be used
using &quot;copy and paste&quot; is EXTREMELY INCONVENIENT
'actually, re-key-in the data is EVEN FASTER then &quot;copy and paste&quot; !
On Error
the info in Sheets(&quot;sticker&quot REQUIRES DOUBLE CHECK
'which is unefficient
End Sub

--------------------------------------------------------------------------------------------------------------------------------
Sub Difficulties()
Since
the LENGTH of product info for EACH sticker in Sheets(&quot;sticker&quot is DIFFERENT
Therefore
the RANGE of EACH STICKER is DIFFERENT
the sizing and positioning for EACH sticker VARIES
'especially the &quot;content&quot;, do not know how to manage its number of row
End Sub
--------------------------------------------------------------------------------------------------------------------------------
Sub DraftSoln()
If the product info in Sheets(&quot;checklist&quot can transfer to Sheets(&quot;sticker&quot _
AUTOMATICALLY and INTELLIGENTLY by vba Then
It will be GREAT
End If
End Sub
--------------------------------------------------------------------------------------------------------------------------------
Sub SolnDirection()
Select Case Direction
Case by FORMULA
Sheets(&quot;sticker&quot.Cells().value can be QUERIED from Sheets(&quot;checklist&quot.Cells
Difficulties
Case vba1
using vba to fill Sheets(&quot;sticker&quot.Cells().value with CORRESPONDING RECORDS in Sheets(&quot;checklist&quot.Cells
Difficulties
Case vba2
using vba to MAKE a new sticker sheet (still using the SAME format as Sheets(&quot;sticker&quot)
'MAKE (CREATE from zero)
'MAKE...like the path how REPORT is made in MSAccess (CREATE from data table)
Difficulties
End Select
End Sub
--------------------------------------------------------------------------------------------------------------------------------
Sub Questions()
any good suggestions?
If Case vba2 Then
how to write the vba codes? (it seems complicated..) ***the size of Print_Area in Sheets(&quot;sticker&quot CANNOT be changed!!!
End If
End Sub

 

[FacilitiesCAD]

Page requested not found.
Reading your note makes me think you understand the logic needed to do what you want to do.

First are all 3 sheets in the same workbook?

It sounds like you shoud be able to put in your &quot;sticer sheet&quot; =checklist!a1 if the information you want is always in a1 on the checklist sheet
if its always in a1 or a2 or a3 and the other two are blank you can say = checklist!a1 & checklist!a2 & checklist!a3

Please try to explain your problem more clearly and in english instead of sub() language. I'll see if I can help more.

Tim

 

[Mutt]

Will look at spreadsheet over the weekend - note to FacilitiesCAD above - have to clear few blanks from end of link to get to it.....
Mutt.

 

[FacilitiesCAD]

Alright, I opened the file and the code you want doesn't look to hard. First I want to thank you because your file showed me vlookup wich I had never seen before.

You can aproach this from a few different ways. Either you can set up a macro that you run after each data entry or one that does the whole sheet every time.
From the looks of it you can have 1 to 5 contents (is this right?)

Sub Problem() - write macro to do the right pasting for you (from data_entry)

Sub Difficulties()- use a loop(for i=1 to 5) and check if the cell is empty =&quot;&quot; this way you can have 1 to 5 contents


DraftSoln() it will be much easyer to take the data from data_entry than from your checklist. The way it is now

here are some things to think about when making the stickers
what is the print area, where do you want to put the information after you fill your first page?

in the simplest case your vba code might look something like this

sum make_sticker()
Dim x as integer 'column
Dim y as integer 'row
Dim x1 as integer 'user input column
Dim y1 as integer 'user input row

dim i as integer 'used in loops


x=1 'initial row
y=4 'initial column


cells(x,y) = &quot;My Company&quot;
cells(x+1,y)=&quot;:&quot;
cells(x+2,y)=&quot;real company name&quot;
y=y+1
cells(x,y) = &quot;Date&quot;
cells(x+1,y)=&quot;:&quot;
cells(x+2,y)=TEXT(TODAY(),&quot;dd/mm/yyyy&quot
y=y+1
cells(x,y) = &quot;Suplier&quot;
cells(x+1,y)=&quot;:&quot;
cells(x+2,y)=data_entry!$A$1
y=y+1

cells(x,y) = &quot;id&quot;
cells(x+1,y)=&quot;:&quot;
etc

I have to lookup one of my macros on how to get some of this information that comes next