Fill Down based on variable, offset?

Hi all and thanks for such an AMAZING site!  You've vicariously gotte
me out of several programming jams in the past, and I'm now a littl
embarrassed to find myself between a code and a hard place.

I am writing (trying, anyway) VBA to do the following:

Once a macro is executed, excel looks at rows that have the followin
information:

ColA       ColB          ColC              ColD
ITEM#   SLOT#  DESCRIPTION   # of Labels
5301      DF212  Black Beans           5
1624      CA172  Rice                       2

I need to tell excel to (on a different sheet) create:

5301
Black Beans
DF212
5301
Black Beans
DF212
5301
Black Beans
DF212
5301
Black Beans
DF212
5301
Black Beans
DF212
1624
Rice
CA172
1624
Rice
CA172

...and so on.  In other words, I need it to repeat the insert of th
Item#, Slot# and Description specified by what is entered for the #o
Labels.

Having been a great fan of this site for some time now, I know bette
than to ask for a simple "Do it for me".  Getting there is half th
fun, so they say.  Any help or pointing in the right direction or hint
or clues or web site references would be incredibly appreciated.

Thanks again for such an awesome forum!

Jim C

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

0
11/26/2003 5:44:15 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
441 Views

Similar Articles

[PageSpeed] 55

well, this isn't from ExcelTip or ExcelForum, but...

I'd suggest a modification. If you were to instead copy the # of 
labels' worth to rows, e.g.:

    5301    Black Beans     DF212
    5301    Black Beans     DF212
    ...

You can then  use Word's data merge feature to easily create labels.

    Public Sub RejiggerDataHorizontally()
        Dim vArr(1 To 1, 1 To 3) As Variant
        Dim rCell As Range
        Dim rDest As Range
        Dim i As Integer
        Set rDest = Sheets("Sheet2").Range("A1")
        For Each rCell In Sheets("Sheet1").Range("A2:A" & _
                Range("A" & Rows.Count).End(xlUp).Row)
            With rCell
                vArr(1, 1) = .Value
                vArr(1, 2) = .Offset(0, 2).Value
                vArr(1, 3) = .Offset(0, 1).Value
                For i = 0 To .Offset(0, 3).Value - 1
                   rDest.Offset(i, 0).Resize(1, 3).Value = vArr
                Next i
                Set rDest = rDest.Offset(i, 0)
            End With
        Next rCell
    End Sub

If instead you want the format you've laid out:

    Public Sub RejiggerDataVertically()
        Dim vArr(1 To 3, 1 To 1) As Variant
        Dim rCell As Range
        Dim rDest As Range
        Dim i As Integer
        Set rDest = Sheets("Sheet2").Range("A1")
        For Each rCell In Sheets("Sheet1").Range("A2:A" & _
                Range("A" & Rows.Count).End(xlUp).Row)
            With rCell
                vArr(1, 1) = .Value
                vArr(2, 1) = .Offset(0, 2).Value
                vArr(3, 1) = .Offset(0, 1).Value
                For i = 0 To .Offset(0, 3).Value - 1
                   rDest.Offset(i * 3, 0).Resize(3, 1).Value = vArr
                Next i
                Set rDest = rDest.Offset(i * 3, 0)
            End With
        Next rCell
    End Sub



In article <CarpeDiemFL.xiicz@excelforum-nospam.com>,
 CarpeDiemFL <CarpeDiemFL.xiicz@excelforum-nospam.com> wrote:

> Hi all and thanks for such an AMAZING site!  You've vicariously gotten
> me out of several programming jams in the past, and I'm now a little
> embarrassed to find myself between a code and a hard place.
> 
> I am writing (trying, anyway) VBA to do the following:
> 
> Once a macro is executed, excel looks at rows that have the following
> information:
> 
> ColA       ColB          ColC              ColD
> ITEM#   SLOT#  DESCRIPTION   # of Labels
> 5301      DF212  Black Beans           5
> 1624      CA172  Rice                       2
> 
> I need to tell excel to (on a different sheet) create:
> 
> 5301
> Black Beans
> DF212
> 5301
> Black Beans
> DF212
> 5301
> Black Beans
> DF212
> 5301
> Black Beans
> DF212
> 5301
> Black Beans
> DF212
> 1624
> Rice
> CA172
> 1624
> Rice
> CA172
> 
> ..and so on.  In other words, I need it to repeat the insert of the
> Item#, Slot# and Description specified by what is entered for the #of
> Labels.
> 
> Having been a great fan of this site for some time now, I know better
> than to ask for a simple "Do it for me".  Getting there is half the
> fun, so they say.  Any help or pointing in the right direction or hints
> or clues or web site references would be incredibly appreciated.
> 
> Thanks again for such an awesome forum!
> 
> Jim C.
> 
> 
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>
0
jemcgimpsey (6723)
11/26/2003 6:41:39 PM
Oops, this may be too close to "do it for me" for you.  Sorry :-)

On the other hand, this is entirely off the top of my head & untested,
so there are probably enough syntax goofs to keep you busy in online Help.
It should at least give you some ideas.

Key to my approach is to avoid using the expensive Select and Activate
methods as much as humanly possible.

*****************************************
Dim wksSource as worksheet
Dim rngTarget as range

Dim lngSourceRows as long     ' # of rows in SourceData
Dim r as Long                           'Current Row

Dim intLabels as integer            '# of labels
Dim i as integer                        'Current Label


Set wksSource = Sheets("SourceData")
'Set target anchor to 1st empty cell on sheet. ** Change cell address if
necessary **
Set rngTarget = Sheets("TargetData").range("A1")

 lngSourceRows  = wksSource.usedrange.rows.count

for r = 1 to lngSourceRows     '**Change starting row if there are headers
in the source data**
     iLabels = wksSource.cells(r,4)
    'Create iLabel # of labels in Column A
    For i = 1 to iLabels            ' **Assumes iLabel value won't EVER be
zero. **
        rngTarget = wksSource.cells(r,1)                       'Copy ColA
value
        rngTarget.offset(1,0) = wksSource.cells(r,3)      ' Copy ColC value
         rngTarget.offset(2,0) = wksSource.cells(r,2)    ' Copy ColB value
        'Set anchor to the next empty cell. **If you want a blank line
before the next label, change to offset(4,0)**
        set rngTarget = rngTarget.offset(3,0)
     next i
Next r
*************************************

Hope this helps, but not too much,
--
George Nicholson

Remove 'Junk' from return address.


"CarpeDiemFL" <CarpeDiemFL.xiicz@excelforum-nospam.com> wrote in message
news:CarpeDiemFL.xiicz@excelforum-nospam.com...
>
> Hi all and thanks for such an AMAZING site!  You've vicariously gotten
> me out of several programming jams in the past, and I'm now a little
> embarrassed to find myself between a code and a hard place.
>
> I am writing (trying, anyway) VBA to do the following:
>
> Once a macro is executed, excel looks at rows that have the following
> information:
>
> ColA       ColB          ColC              ColD
> ITEM#   SLOT#  DESCRIPTION   # of Labels
> 5301      DF212  Black Beans           5
> 1624      CA172  Rice                       2
>
> I need to tell excel to (on a different sheet) create:
>
> 5301
> Black Beans
> DF212
> 5301
> Black Beans
> DF212
> 5301
> Black Beans
> DF212
> 5301
> Black Beans
> DF212
> 5301
> Black Beans
> DF212
> 1624
> Rice
> CA172
> 1624
> Rice
> CA172
>
> ..and so on.  In other words, I need it to repeat the insert of the
> Item#, Slot# and Description specified by what is entered for the #of
> Labels.
>
> Having been a great fan of this site for some time now, I know better
> than to ask for a simple "Do it for me".  Getting there is half the
> fun, so they say.  Any help or pointing in the right direction or hints
> or clues or web site references would be incredibly appreciated.
>
> Thanks again for such an awesome forum!
>
> Jim C.
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>


0
JunkGeorgeN (154)
11/26/2003 7:25:03 PM
Reply:

Similar Artilces:

filling cells
i have a colum of cells that depending on a vlookup are either empty o have text. I want to put these cells in a combo box but i don't wan to have all the empty cells. the only way i could figue was to us this formula =IF(Y7="",Y8,IF(Y8="",Y9,IF(Y9="",Y10))) only problem is i have over 600 cells i want to do this with. so i there any way to do this that i'm not thinking of without the typin cramps?? hope someone can hel -- Neuthe ----------------------------------------------------------------------- Neuther's Profile: http://www.excelforum.co...

Vlookup based on multiple criteria
I have 2 workbooks: Workbook one: Account number, name, January to September + total (columns A9 to L240) Workbook two: I need to populate with data from Workbook one Column A has the account numbers all accounts that contains department 71(xxxxx-71-xx) must go to location 1 (Jan - column B, Feb =96 column E etc) and all others goes to location 4 (Jan =96 column C, Feb =96 column F etc) Is there a basic a VLOOKUP or other function that can automatic the tasks? Thanks in advance. Workbook one Account Number Jan-10 Feb-10 Mar-10 41001-71-01 6,000.00 - 1,000.00 41001-71...

value of value of a variable.
I tried searching, but no use! I have a Const NameA = "BLA BLA" I have a variable NameB Value of NameB is NameA. How do i get the text "BLA BLA" from NameB variable Is there anyway to do that? something like,, VALUE(NameB) Thanks & Regards Joe Hi Dim NameB as String NameB =3D NameA in a cell you could have range("A1").Value =3D NameB would now have content "BLA BLA" regards Paul On May 21, 12:35=A0pm, Joe <joe.varghese.j...@gmail.com> wrote: > I tried searching, but no use! > > I have a Const Name...

how to automatically run a report with parameters and email the report based on
Hi all, I have a report which uses a username as an input parameter. I need to schedule the report to run automatically and email only the page of the report which belongs to each user, to that user's email address. The stored procedure I'm using for the report already uses the username as an input parameter. The problem is that I cannot automatically run the report for each of the parameter values separately. Thanks in advance... ...

populating sheets based on data from parent sheets
Hello, I have learned much from the contributors here...many thanks. Challenge: To have one spreadsheet (master) trigger the populating of another spreadsheet (sheet2) when data is entered into a cell on the master sheet. Once certain data is entered into a cell on Sheet2, it would populate data onto sheet3. For example: When a date is entered into the "scheduled delivery date" field, on the master sheet. I would like for this to "trigger" chosen data to be entered into to the "delivery data sheet". Once a delivery has been confirmed (by entering a date int...

Conditional Formatting based on Formula
The built-in rules for conditional formatting (greater than value, less than value, etc) work on the cell for which the CF is applied WITHOUT having to specify the cell. The examples for advanced rules using a formula require the formulas to respect their arguments. For instance, I'd like to have a CF that highlights numbers, so I'd rely on "ISNUMBER()" to get a true/false value that triggers the format. However, ISNUMBER() requires an argument, and I have been able to enter the cell's address as an argument to get it to work: apply CF to $A$1 by entering "=I...

Deriving a name from variables
I have 22 CheckBox objects named CheckBox1, CheckBox2 etc. I would like to substitue a variable to represent the name in the expression "Sheet2.TextBox1= False" by using a do loop and varying the number portion of the word TextBox. Any suggestions? -- Bernie Checkboxes and textboxes from the control toolbox toolbar??? I'm not quite sure what you're doing, but maybe this will give you an idea: Option Explicit Sub testme() Dim iCtr As Long With Sheet2 For iCtr = 1 To 22 .OLEObjects("textbox" & iCtr).Object.Enabl...

How to fill fill a column with numbers, beginning at number X, counting up.
I simply need to add numbers, beginning with 15,347, (counting upward), to a column with empty values in a table. Is there an easy way to do this, rather than completing it in excel and importing it, then attempting to update the table? Thanks for any suggestions. While I cannot imagine a legitimate use for this, the following code will insert rows starting at 15347 and going to 22000 Dim dbCurr As DAO.Database Dim lngLoop As Long Dim strSQL As String Set dbCurr = CurrentDb For lngLoop = 15347 To 22000 strSQL = "INSERT INTO MyTable (MyField) " & _ "VALUE...

Converting variable text strings to numeric
I've imported some word tables into excel, the 'numbers' have spaces instead of commas and consequently are read as text. The 'numbers' vary from single integer to millions For example 1 101 335 293 207 23 113 101 19 While I can use the left, mid and right functions for the millions that formula is unworkable for other numbers. Any ideas, or do I have to get a VB programmer to write a function for me? Hi! Try using Find/Replace Select the range Goto Edit>Replace Find What: enter a space by hitting the space bar Replace With: nothing...

grouping mails based "read" status
I want to group mails based on the read status. UnRead mails should b one group and read messages in another group. I searched for th "Customizing Views", but I could not figure. Any idea from the group? Ramesh ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com I don't think you can group by read status. You could sort by the Icon column... -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to t...

Applying GPOs based on Operating System Version (item-level targetting)
Hi, we have a Win2003 AD with one server acting as the PDC and a small bunch of GPOs. All clients are running WinXP SP3. The whole network / AD is well working. Our management now wants Windows 7, but only on their computers, we're getting a WinXP/Win7 mixed environment (thanks Boss!). Thus we have to split some GPOs (eg. Folder Redirection, etc.). We could clone each security group, one for XP users/computers and the other for Win7 and apply GPOs only to the correct group but that might not be the best solution. We would like to use the item-level targetting like in dri...

Auto fill drop down boxes
Is there a way to delete a name that appears twice in a drop down auto fill box? Hi Fred This should not happen. Each item in the dropdown list should be unique. What probably is the poroblem, is that some of 'apparently the same' entries, maybe have a space after the last character. Try the Trim function on your data. Yours, Mathew "Fred" <fredlh@attbi.com> wrote in message news:005901c38cf0$341a89e0$a401280a@phx.gbl... > Is there a way to delete a name that appears twice in a > drop down auto fill box? --- Outgoing mail is certified Virus Free. Checked by AV...

Calendar Formatting Based Upon Multple Date Ranges
Hi there, what I am after is simple enough in nature, but seems to be difficult to create. Basically I work on a rotational job. Usually it is 4 weeks at site, and 4 weeks off. Sometimes that can Change to 6 on, 2 off, etc. I'd like to create a calendar that allows me to specify a start date, # days on, # days off, and have it format a calendar with simple colors for days working. I don't care what has happened up to this point in the year - just from the 'start day' forward. any suggestions would be appreciated. Try this http://spreadsheets.google.com/ccc?key=...

Move a dialog based application without the title bar
Hi, I have created a dialog based application without a title bar, and want to move the dialog using the left mouse button. I tried MoveWindow nut the window was streching instead of moving !!! Can anyone help me please? >I have created a dialog based application without a title bar, and >want to move the dialog using the left mouse button. >I tried MoveWindow nut the window was streching instead of moving !!! MoveWindow can resize and move the window, so you've got a coding problem. If you use SetWindowPos you can specify the SWP_NOSIZE option to have it ignore the cx & c...

Set FormName with variable in VBA
Hi there , Maybe the subject is not clear , but i'll try to explain here. I'd like to change the formname in the next code with a variable : ex : Forms!FrmOrders.Controls("CmdAdd").Caption = "Add" change it in : Forms!VARIABLENAME.Controls("CmdAdd").Caption = "Add" I get the formname from a table/query I tried to do this , but it always gives me an error. Can anyone help me with this. Thanks Mario Hello again , I've just read the HELP (F1) in Access , and found the solution there. Just use brackets around the variablename Forms(VARIABL...

sessionclass.variable syntax?
What is the correct syntax for using the SessionClass.Variable property, specifically to set it to a particular value so I can then use this value in another add-in that fires later Thank you kevin ...

Create a report based on information from two forms
Hi I need to create several reports which have the appearance of the forms in my database. The top half of the form/report contains information that must be on every report so I created a base report. In the top half, I have formulas which are correct. This report/form is called Students. The bottom half of the form is in reality a subform. What I've done is saved the forms as reports (using the same file name except that these names start with R for report), Then I saved the R-Student report with a new name so I can combine the information for the second half. (...

Outlook rule based on value NOT in name?
Hi, I have a lot of SPAM coming in with the sender's address set to my e-mail address. However, the name is different. I would like to setup a rule like this: If the sender's e-mail address = XXXXXXX, but the sender's name <> YYYYYYYY then move to the junk folder. Can this be done? All I see are rules to look for a string - not look for the absence of a string. I use e-mail to transfer notes to myself and also transfer information between mail accounts and computers, so I do send myself e-mail fairly frequently. I'm using Outlook 2003. Thank you for your time! -...

Creating calendar based on agency program data
I am working with a group of social service agencies, who want to create a joint community calendar of programs. Their vision is to have a calendar with programs coded by age group, time of day, day of week, and other information. i could easily group this data in a simple Access database and produce a simple report, but they want the output in a visually useful way, such as a monthly calendar. Can one produce a calendar report based on data in a database? We would need to keep this data constantly updated. Thanks. ...

Creating Formula with Embedded Variable ID
I need to create a single formula for my workbook that references cells from various tabs (over 20) in another workbook. I want to copy this formula into multiple tabs within my workbook. My workbook and the external workbook have the same number of and identically named tabs. I want to embed a variable into the formula that will take on the name of the tab that the formula is copied to. I imagine the formula would look something like this: ='K:\Sales Reports\[Ohio Team, Mar-21-2004.xls]variable_identifying_the_tab_goes_here'!D1 I know I can use the following formula to derive th...

Button on Form to Run Report based on Query with Parameters
Hi Guys. I am busy working on a stock control database (and job control). When I have a Form Open, it shows several fields, including JobID (Which is the PK for that job) (frm_Invoicing) I also have a Query that when Run, it prompts for the JobIDNo. (qry_CustJobsInvStock) I have a report based on that query. (rpt_CustJobsInvStock) I have added a button onto the form (onclick -> DoCmd.OpenReport) to open and automatically print the report, But I just cannot figure out how to get it to get my JobID from the form, and to Automatically add it as a Parameter for the que...

VBA Filter (with many variables) and copy result to new Worksheets
Using XL 2003 and 97 Two columns (fields) to be used to filter For Data set #1: ColumnA Division (Choices) A B C ColumnB Emp# (Choices are numbers and ALL need to be considered at once) From 4142 to 4143 From 4500 to 5001 From 4509 to 4512 From 6000 to 7000 4122, 4125, 4161, 4177 4803 I do know how to VBA-code a two column (field) filter. That said, how do I handle the multiple ColumnB possibilities? In reality, there are 23 more Emp#'s than I listed above. I know that there is a limit (7?) to variables includible in the AND and OR functions. There ar...

internet based services policy
Today when i opened money i got a pop-up about microsoft money 2006 internet based services policy. I click Decline because i don't want it and i don't want microsoft to "from time to time without notice automatically install updates" and MONEY CLOSES!!!! WHAT THE HELL!!! I can't open it without clicking Accept!! -- bill ...

How do you add based on base 60 (minutes) instead of 100
Want to add a column with hours and minutes as 5.32 meaning 5 hours and 32 minutes Enter it in time format, 5:32 Make sure that the total cell is formatted as [hh]:mm to cater for more than 24 hours. -- HTH Bob Phillips "Mick Stohr" <Mick Stohr@discussions.microsoft.com> wrote in message news:1EF63D80-421D-44B3-860A-12A50BA2657C@microsoft.com... > Want to add a column with hours and minutes as 5.32 meaning 5 hours and 32 > minutes =SUM(--SUBSTITUTE(IF(A2:A100<>"",A2:A100&".0","0.0"),".",":"))*24 array ...

Fill Colors in Publisher 2000
Fill colors dissapear. I am using a calander and in some dates I am filling in different colors. For the most part it works fine, but occassionally when I go to fill in a new date all the dates filled with color lose their color. This will also happen with colored text as the test will default back to black. ...