Lookup a range and insert range name into second worksheet

Hi,

Worksheet 1
Column B           Column K

Delivery x                 Allocation A
Delivery x                 Allocation A
Delivery x                 Allocation B
Delivery x                 Allocation C
Delivery x                 Allocation C
Delivery x                Allocation C
Delivery x                Allocation D

Worksheet 2
               COL 1          Col 2             Col 3              Col
4
ROW 13   Allocation A  Allocation B  Allocation C    Allocation D

I need to be able to automatically insert the allocation name in wks2
as it is added in wks1. The allocations cannot be pre named because one
or more labels is needed depending on the volume of the deliveries.
Once the volume is complete it becomes a "single unit" that retains the
same label name.

I have looked at Vlookup, index & match plus some vba using last cell
but can't seem to get to a solution.

Any help appreciated.

0
9/28/2006 4:30:55 AM
excel 39879 articles. 2 followers. Follow

6 Replies
410 Views

Similar Articles

[PageSpeed] 46

Hi

It's not terribly clear what you want.  Could you perhaps clarify?
 - Are the delivery x entries all identical?
 - Do you want spreadsheet 2 literally to say "Row 13"?  What does this 
refer to?
 - When you say automatically update, can you explain how it would be used?  
Someone enters what, where and how.  Then what happens and where?
 - What are you trying to achieve? - there may be other ways of doing this.

Sorry not to be of any help.

Glenton


"shazmar" wrote:

> Hi,
> 
> Worksheet 1
> Column B           Column K
> 
> Delivery x                 Allocation A
> Delivery x                 Allocation A
> Delivery x                 Allocation B
> Delivery x                 Allocation C
> Delivery x                 Allocation C
> Delivery x                Allocation C
> Delivery x                Allocation D
> 
> Worksheet 2
>                COL 1          Col 2             Col 3              Col
> 4
> ROW 13   Allocation A  Allocation B  Allocation C    Allocation D
> 
> I need to be able to automatically insert the allocation name in wks2
> as it is added in wks1. The allocations cannot be pre named because one
> or more labels is needed depending on the volume of the deliveries.
> Once the volume is complete it becomes a "single unit" that retains the
> same label name.
> 
> I have looked at Vlookup, index & match plus some vba using last cell
> but can't seem to get to a solution.
> 
> Any help appreciated.
> 
> 
0
glenton1 (9)
9/28/2006 4:34:02 PM
Ok - sorry yes I guess it isn't very clear. I'll try again.

Delivery x are the deliveries of manure, sawdust coir etc which come in
separately and in varying quantities. These "ingredients" are all put
together into a "windrow" - a huge pile which composts down. The
individual deliveries are recorded and each one is allocated to a
windrow.

What I need to do on sheet 1 is record the allocation name against each
delivery (there may be any number - hence I put allocation A twice and
allocation C three times). Sheet 2 records all activities (i.e. turns)
on each windrow (which by this time is now being recorded as one unit).
So the allocation name has to automatically transfer over to sheet 2
and be inserted in the next availble column in a certain row. All data
relating to this windrow is then captured in that column. Council
requirements need each windrow to be back tracked to each raw material.

I had thought with vba something like iterating through each allocation
name with the last "block" then being recognised as the entry for the
next free spot on the row. However my vba skills aren't up to that.

It seems like the kind of thing that might often be required but I
can't seem to find a way to do it. Unfortunately because of the unknown
quantity of the deliveries and varying size of windrows I can't pre
allocate - it has to remain dynamic.

Hope this is clearer.


glenton wrote:
> Hi
>
> It's not terribly clear what you want.  Could you perhaps clarify?
>  - Are the delivery x entries all identical?
>  - Do you want spreadsheet 2 literally to say "Row 13"?  What does this
> refer to?
>  - When you say automatically update, can you explain how it would be used?
> Someone enters what, where and how.  Then what happens and where?
>  - What are you trying to achieve? - there may be other ways of doing this.
>
> Sorry not to be of any help.
>
> Glenton
>
>
> "shazmar" wrote:
>
> > Hi,
> >
> > Worksheet 1
> > Column B           Column K
> >
> > Delivery x                 Allocation A
> > Delivery x                 Allocation A
> > Delivery x                 Allocation B
> > Delivery x                 Allocation C
> > Delivery x                 Allocation C
> > Delivery x                Allocation C
> > Delivery x                Allocation D
> >
> > Worksheet 2
> >                COL 1          Col 2             Col 3              Col
> > 4
> > ROW 13   Allocation A  Allocation B  Allocation C    Allocation D
> >
> > I need to be able to automatically insert the allocation name in wks2
> > as it is added in wks1. The allocations cannot be pre named because one
> > or more labels is needed depending on the volume of the deliveries.
> > Once the volume is complete it becomes a "single unit" that retains the
> > same label name.
> >
> > I have looked at Vlookup, index & match plus some vba using last cell
> > but can't seem to get to a solution.
> > 
> > Any help appreciated.
> > 
> >

0
9/29/2006 1:10:21 AM
Hi

I'm still not 100% sure that I've got it, but please try the following (& as 
always, pls save before running any macro).

In brief what I've done is added a column in Sheet1 which keeps track of 
whether the information has been transferred to Sheet2 or not.

You'll need to set the MyCol1, MyCol2, MyCol3, MyRow1, MyRow2 parameters in 
the macro (there are comments there to help you do that).  MyCol3 will simply 
be the column number of the first free column you have in Sheet1.  When 
entering data simply leave this blank.  The macro will change it to TRUE when 
it's copied that data across to Sheet2

I recommend stepping through the macro (with F8) to get a feel for what it's 
doing.

Sub MyTransfer()

Dim MySh1 As Worksheet
Dim MySh2 As Worksheet
Dim MyCol1 As Integer
Dim MyCol2 As Integer
Dim MyCol3 As Integer

Set MySh1 = Sheets("Sheet1")
Set MySh2 = Sheets("Sheet2")
MyCol1 = 1 'column in Sheet1 where description is
MyCol2 = 2 'column in Sheet1 where allocation name is
MyCol3 = 3 'extra column in Sheet1 to keep track of what information has 
been transferred to Sheet2
MyRow1 = 4 'row in Sheet1 where data starts
MyRow2 = 3 'row in Sheet2 where allocation headings are

'Run a loop through all the rows of sheet1
Do While MySh1.Cells(MyRow1, MyCol2) <> ""

'Check if data has already been transferred to sheet2
If Not MySh1.Cells(MyRow1, MyCol3) Then

'Check if Allocation name has already been used
MyAllName = MySh1.Cells(MyRow1, MyCol2)
Set MyAll = MySh2.Range("a1:iv1").Offset(MyRow2 - 1, 0).Find(MyAllName)
If MyAll Is Nothing Then
j = 1
Do Until MySh2.Cells(MyRow2, j) = ""
j = j + 1
If j > 255 Then
MsgBox "No more columns for allocations!"
Exit Sub
End If
Loop
MySh2.Cells(MyRow2, j) = MySh1.Cells(MyRow1, MyCol2)
i = MyRow2 + 1
Else
j = MyAll.Column
i = MyRow2 + 1
Do Until MySh2.Cells(i, j) = ""
i = i + 1
If i > 50000 Then
MsgBox "No more rows for allocation called " & MySh1.Cells(MyRow1, MyCol2)
Exit Sub
End If
Loop
End If
MySh2.Cells(i, j) = MySh1.Cells(MyRow1, MyCol1)
MySh1.Cells(MyRow1, MyCol3) = True

End If
MyRow1 = MyRow1 + 1

Loop

End Sub

I hope this helps

-- 
Glenton
www.leviqqio.com
Quality financial modelling


"shazmar" wrote:

> Ok - sorry yes I guess it isn't very clear. I'll try again.
> 
> Delivery x are the deliveries of manure, sawdust coir etc which come in
> separately and in varying quantities. These "ingredients" are all put
> together into a "windrow" - a huge pile which composts down. The
> individual deliveries are recorded and each one is allocated to a
> windrow.
> 
> What I need to do on sheet 1 is record the allocation name against each
> delivery (there may be any number - hence I put allocation A twice and
> allocation C three times). Sheet 2 records all activities (i.e. turns)
> on each windrow (which by this time is now being recorded as one unit).
> So the allocation name has to automatically transfer over to sheet 2
> and be inserted in the next availble column in a certain row. All data
> relating to this windrow is then captured in that column. Council
> requirements need each windrow to be back tracked to each raw material.
> 
> I had thought with vba something like iterating through each allocation
> name with the last "block" then being recognised as the entry for the
> next free spot on the row. However my vba skills aren't up to that.
> 
> It seems like the kind of thing that might often be required but I
> can't seem to find a way to do it. Unfortunately because of the unknown
> quantity of the deliveries and varying size of windrows I can't pre
> allocate - it has to remain dynamic.
> 
> Hope this is clearer.
> 
> 
> glenton wrote:
> > Hi
> >
> > It's not terribly clear what you want.  Could you perhaps clarify?
> >  - Are the delivery x entries all identical?
> >  - Do you want spreadsheet 2 literally to say "Row 13"?  What does this
> > refer to?
> >  - When you say automatically update, can you explain how it would be used?
> > Someone enters what, where and how.  Then what happens and where?
> >  - What are you trying to achieve? - there may be other ways of doing this.
> >
> > Sorry not to be of any help.
> >
> > Glenton
> >
> >
> > "shazmar" wrote:
> >
> > > Hi,
> > >
> > > Worksheet 1
> > > Column B           Column K
> > >
> > > Delivery x                 Allocation A
> > > Delivery x                 Allocation A
> > > Delivery x                 Allocation B
> > > Delivery x                 Allocation C
> > > Delivery x                 Allocation C
> > > Delivery x                Allocation C
> > > Delivery x                Allocation D
> > >
> > > Worksheet 2
> > >                COL 1          Col 2             Col 3              Col
> > > 4
> > > ROW 13   Allocation A  Allocation B  Allocation C    Allocation D
> > >
> > > I need to be able to automatically insert the allocation name in wks2
> > > as it is added in wks1. The allocations cannot be pre named because one
> > > or more labels is needed depending on the volume of the deliveries.
> > > Once the volume is complete it becomes a "single unit" that retains the
> > > same label name.
> > >
> > > I have looked at Vlookup, index & match plus some vba using last cell
> > > but can't seem to get to a solution.
> > > 
> > > Any help appreciated.
> > > 
> > >
> 
> 
0
glenton1 (9)
9/29/2006 9:32:02 AM
Hi,

Many thanks for your help.

I'm getting an application-defined or object-defined error "1004" at
the following

Do While MySh1.Cells(MyRow1, MyCol2) <> ""

I've set up as follows - please see the *NOTES* I've put in

Sub MyTransfer()

Dim MySh1 As Worksheet
Dim MySh2 As Worksheet
Dim MyCol1 As Integer
Dim MyCol2 As Integer
Dim MyCol3 As Integer
Dim MyRow1 As Integer
Dim MyRow2 As Integer


Set MySh1 = Sheets("WindRow_Control")
Set MySh2 = Sheets("Windrow_Turns")

MyCol1 = K 'column in Sheet1 where description is *NOTE* I don't need
this column but inserted it for this exercise
MyCol2 = l 'column in Sheet1 where allocation name is *NOTE* - vba
editor keeps changing this L to lower case l - is this the problem?

MyCol3 = M 'extra column in Sheet1 to keep track of what information
has
'been transferred to Sheet2
MyRow1 = 11 'row in Sheet1 where data starts
MyRow2 = 13 'row in Sheet2 where allocation headings are


'Run a loop through all the rows of sheet1
Do While MySh1.Cells(MyRow1, MyCol2) <> ""



glenton wrote:
> Hi
>
> I'm still not 100% sure that I've got it, but please try the following (& as
> always, pls save before running any macro).
>
> In brief what I've done is added a column in Sheet1 which keeps track of
> whether the information has been transferred to Sheet2 or not.
>
> You'll need to set the MyCol1, MyCol2, MyCol3, MyRow1, MyRow2 parameters in
> the macro (there are comments there to help you do that).  MyCol3 will simply
> be the column number of the first free column you have in Sheet1.  When
> entering data simply leave this blank.  The macro will change it to TRUE when
> it's copied that data across to Sheet2
>
> I recommend stepping through the macro (with F8) to get a feel for what it's
> doing.
>
> Sub MyTransfer()
>
> Dim MySh1 As Worksheet
> Dim MySh2 As Worksheet
> Dim MyCol1 As Integer
> Dim MyCol2 As Integer
> Dim MyCol3 As Integer
>
> Set MySh1 = Sheets("Sheet1")
> Set MySh2 = Sheets("Sheet2")
> MyCol1 = 1 'column in Sheet1 where description is
> MyCol2 = 2 'column in Sheet1 where allocation name is
> MyCol3 = 3 'extra column in Sheet1 to keep track of what information has
> been transferred to Sheet2
> MyRow1 = 4 'row in Sheet1 where data starts
> MyRow2 = 3 'row in Sheet2 where allocation headings are
>
> 'Run a loop through all the rows of sheet1
> Do While MySh1.Cells(MyRow1, MyCol2) <> ""
>
> 'Check if data has already been transferred to sheet2
> If Not MySh1.Cells(MyRow1, MyCol3) Then
>
> 'Check if Allocation name has already been used
> MyAllName = MySh1.Cells(MyRow1, MyCol2)
> Set MyAll = MySh2.Range("a1:iv1").Offset(MyRow2 - 1, 0).Find(MyAllName)
> If MyAll Is Nothing Then
> j = 1
> Do Until MySh2.Cells(MyRow2, j) = ""
> j = j + 1
> If j > 255 Then
> MsgBox "No more columns for allocations!"
> Exit Sub
> End If
> Loop
> MySh2.Cells(MyRow2, j) = MySh1.Cells(MyRow1, MyCol2)
> i = MyRow2 + 1
> Else
> j = MyAll.Column
> i = MyRow2 + 1
> Do Until MySh2.Cells(i, j) = ""
> i = i + 1
> If i > 50000 Then
> MsgBox "No more rows for allocation called " & MySh1.Cells(MyRow1, MyCol2)
> Exit Sub
> End If
> Loop
> End If
> MySh2.Cells(i, j) = MySh1.Cells(MyRow1, MyCol1)
> MySh1.Cells(MyRow1, MyCol3) = True
>
> End If
> MyRow1 = MyRow1 + 1
>
> Loop
>
> End Sub
>
> I hope this helps
>
> --
> Glenton
> www.leviqqio.com
> Quality financial modelling
>
>
> "shazmar" wrote:
>
> > Ok - sorry yes I guess it isn't very clear. I'll try again.
> >
> > Delivery x are the deliveries of manure, sawdust coir etc which come in
> > separately and in varying quantities. These "ingredients" are all put
> > together into a "windrow" - a huge pile which composts down. The
> > individual deliveries are recorded and each one is allocated to a
> > windrow.
> >
> > What I need to do on sheet 1 is record the allocation name against each
> > delivery (there may be any number - hence I put allocation A twice and
> > allocation C three times). Sheet 2 records all activities (i.e. turns)
> > on each windrow (which by this time is now being recorded as one unit).
> > So the allocation name has to automatically transfer over to sheet 2
> > and be inserted in the next availble column in a certain row. All data
> > relating to this windrow is then captured in that column. Council
> > requirements need each windrow to be back tracked to each raw material.
> >
> > I had thought with vba something like iterating through each allocation
> > name with the last "block" then being recognised as the entry for the
> > next free spot on the row. However my vba skills aren't up to that.
> >
> > It seems like the kind of thing that might often be required but I
> > can't seem to find a way to do it. Unfortunately because of the unknown
> > quantity of the deliveries and varying size of windrows I can't pre
> > allocate - it has to remain dynamic.
> >
> > Hope this is clearer.
> >
> >
> > glenton wrote:
> > > Hi
> > >
> > > It's not terribly clear what you want.  Could you perhaps clarify?
> > >  - Are the delivery x entries all identical?
> > >  - Do you want spreadsheet 2 literally to say "Row 13"?  What does this
> > > refer to?
> > >  - When you say automatically update, can you explain how it would be used?
> > > Someone enters what, where and how.  Then what happens and where?
> > >  - What are you trying to achieve? - there may be other ways of doing this.
> > >
> > > Sorry not to be of any help.
> > >
> > > Glenton
> > >
> > >
> > > "shazmar" wrote:
> > >
> > > > Hi,
> > > >
> > > > Worksheet 1
> > > > Column B           Column K
> > > >
> > > > Delivery x                 Allocation A
> > > > Delivery x                 Allocation A
> > > > Delivery x                 Allocation B
> > > > Delivery x                 Allocation C
> > > > Delivery x                 Allocation C
> > > > Delivery x                Allocation C
> > > > Delivery x                Allocation D
> > > >
> > > > Worksheet 2
> > > >                COL 1          Col 2             Col 3              Col
> > > > 4
> > > > ROW 13   Allocation A  Allocation B  Allocation C    Allocation D
> > > >
> > > > I need to be able to automatically insert the allocation name in wks2
> > > > as it is added in wks1. The allocations cannot be pre named because one
> > > > or more labels is needed depending on the volume of the deliveries.
> > > > Once the volume is complete it becomes a "single unit" that retains the
> > > > same label name.
> > > >
> > > > I have looked at Vlookup, index & match plus some vba using last cell
> > > > but can't seem to get to a solution.
> > > > 
> > > > Any help appreciated.
> > > > 
> > > >
> > 
> >

0
9/29/2006 6:57:14 PM
Hi

Make it
MyCol1 = 11
MyCol2 = 12
MyCol3 = 13

rather than K,L & M (i.e. the column number, rather than the column label.)

Regards
-- 
Glenton
www.leviqqio.com
Quality financial modelling


"shazmar" wrote:

> Hi,
> 
> Many thanks for your help.
> 
> I'm getting an application-defined or object-defined error "1004" at
> the following
> 
> Do While MySh1.Cells(MyRow1, MyCol2) <> ""
> 
> I've set up as follows - please see the *NOTES* I've put in
> 
> Sub MyTransfer()
> 
> Dim MySh1 As Worksheet
> Dim MySh2 As Worksheet
> Dim MyCol1 As Integer
> Dim MyCol2 As Integer
> Dim MyCol3 As Integer
> Dim MyRow1 As Integer
> Dim MyRow2 As Integer
> 
> 
> Set MySh1 = Sheets("WindRow_Control")
> Set MySh2 = Sheets("Windrow_Turns")
> 
> MyCol1 = K 'column in Sheet1 where description is *NOTE* I don't need
> this column but inserted it for this exercise
> MyCol2 = l 'column in Sheet1 where allocation name is *NOTE* - vba
> editor keeps changing this L to lower case l - is this the problem?
> 
> MyCol3 = M 'extra column in Sheet1 to keep track of what information
> has
> 'been transferred to Sheet2
> MyRow1 = 11 'row in Sheet1 where data starts
> MyRow2 = 13 'row in Sheet2 where allocation headings are
> 
> 
> 'Run a loop through all the rows of sheet1
> Do While MySh1.Cells(MyRow1, MyCol2) <> ""
> 
> 
> 
> glenton wrote:
> > Hi
> >
> > I'm still not 100% sure that I've got it, but please try the following (& as
> > always, pls save before running any macro).
> >
> > In brief what I've done is added a column in Sheet1 which keeps track of
> > whether the information has been transferred to Sheet2 or not.
> >
> > You'll need to set the MyCol1, MyCol2, MyCol3, MyRow1, MyRow2 parameters in
> > the macro (there are comments there to help you do that).  MyCol3 will simply
> > be the column number of the first free column you have in Sheet1.  When
> > entering data simply leave this blank.  The macro will change it to TRUE when
> > it's copied that data across to Sheet2
> >
> > I recommend stepping through the macro (with F8) to get a feel for what it's
> > doing.
> >
> > Sub MyTransfer()
> >
> > Dim MySh1 As Worksheet
> > Dim MySh2 As Worksheet
> > Dim MyCol1 As Integer
> > Dim MyCol2 As Integer
> > Dim MyCol3 As Integer
> >
> > Set MySh1 = Sheets("Sheet1")
> > Set MySh2 = Sheets("Sheet2")
> > MyCol1 = 1 'column in Sheet1 where description is
> > MyCol2 = 2 'column in Sheet1 where allocation name is
> > MyCol3 = 3 'extra column in Sheet1 to keep track of what information has
> > been transferred to Sheet2
> > MyRow1 = 4 'row in Sheet1 where data starts
> > MyRow2 = 3 'row in Sheet2 where allocation headings are
> >
> > 'Run a loop through all the rows of sheet1
> > Do While MySh1.Cells(MyRow1, MyCol2) <> ""
> >
> > 'Check if data has already been transferred to sheet2
> > If Not MySh1.Cells(MyRow1, MyCol3) Then
> >
> > 'Check if Allocation name has already been used
> > MyAllName = MySh1.Cells(MyRow1, MyCol2)
> > Set MyAll = MySh2.Range("a1:iv1").Offset(MyRow2 - 1, 0).Find(MyAllName)
> > If MyAll Is Nothing Then
> > j = 1
> > Do Until MySh2.Cells(MyRow2, j) = ""
> > j = j + 1
> > If j > 255 Then
> > MsgBox "No more columns for allocations!"
> > Exit Sub
> > End If
> > Loop
> > MySh2.Cells(MyRow2, j) = MySh1.Cells(MyRow1, MyCol2)
> > i = MyRow2 + 1
> > Else
> > j = MyAll.Column
> > i = MyRow2 + 1
> > Do Until MySh2.Cells(i, j) = ""
> > i = i + 1
> > If i > 50000 Then
> > MsgBox "No more rows for allocation called " & MySh1.Cells(MyRow1, MyCol2)
> > Exit Sub
> > End If
> > Loop
> > End If
> > MySh2.Cells(i, j) = MySh1.Cells(MyRow1, MyCol1)
> > MySh1.Cells(MyRow1, MyCol3) = True
> >
> > End If
> > MyRow1 = MyRow1 + 1
> >
> > Loop
> >
> > End Sub
> >
> > I hope this helps
> >
> > --
> > Glenton
> > www.leviqqio.com
> > Quality financial modelling
> >
> >
> > "shazmar" wrote:
> >
> > > Ok - sorry yes I guess it isn't very clear. I'll try again.
> > >
> > > Delivery x are the deliveries of manure, sawdust coir etc which come in
> > > separately and in varying quantities. These "ingredients" are all put
> > > together into a "windrow" - a huge pile which composts down. The
> > > individual deliveries are recorded and each one is allocated to a
> > > windrow.
> > >
> > > What I need to do on sheet 1 is record the allocation name against each
> > > delivery (there may be any number - hence I put allocation A twice and
> > > allocation C three times). Sheet 2 records all activities (i.e. turns)
> > > on each windrow (which by this time is now being recorded as one unit).
> > > So the allocation name has to automatically transfer over to sheet 2
> > > and be inserted in the next availble column in a certain row. All data
> > > relating to this windrow is then captured in that column. Council
> > > requirements need each windrow to be back tracked to each raw material.
> > >
> > > I had thought with vba something like iterating through each allocation
> > > name with the last "block" then being recognised as the entry for the
> > > next free spot on the row. However my vba skills aren't up to that.
> > >
> > > It seems like the kind of thing that might often be required but I
> > > can't seem to find a way to do it. Unfortunately because of the unknown
> > > quantity of the deliveries and varying size of windrows I can't pre
> > > allocate - it has to remain dynamic.
> > >
> > > Hope this is clearer.
> > >
> > >
> > > glenton wrote:
> > > > Hi
> > > >
> > > > It's not terribly clear what you want.  Could you perhaps clarify?
> > > >  - Are the delivery x entries all identical?
> > > >  - Do you want spreadsheet 2 literally to say "Row 13"?  What does this
> > > > refer to?
> > > >  - When you say automatically update, can you explain how it would be used?
> > > > Someone enters what, where and how.  Then what happens and where?
> > > >  - What are you trying to achieve? - there may be other ways of doing this.
> > > >
> > > > Sorry not to be of any help.
> > > >
> > > > Glenton
> > > >
> > > >
> > > > "shazmar" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > Worksheet 1
> > > > > Column B           Column K
> > > > >
> > > > > Delivery x                 Allocation A
> > > > > Delivery x                 Allocation A
> > > > > Delivery x                 Allocation B
> > > > > Delivery x                 Allocation C
> > > > > Delivery x                 Allocation C
> > > > > Delivery x                Allocation C
> > > > > Delivery x                Allocation D
> > > > >
> > > > > Worksheet 2
> > > > >                COL 1          Col 2             Col 3              Col
> > > > > 4
> > > > > ROW 13   Allocation A  Allocation B  Allocation C    Allocation D
> > > > >
> > > > > I need to be able to automatically insert the allocation name in wks2
> > > > > as it is added in wks1. The allocations cannot be pre named because one
> > > > > or more labels is needed depending on the volume of the deliveries.
> > > > > Once the volume is complete it becomes a "single unit" that retains the
> > > > > same label name.
> > > > >
> > > > > I have looked at Vlookup, index & match plus some vba using last cell
> > > > > but can't seem to get to a solution.
> > > > > 
> > > > > Any help appreciated.
> > > > > 
> > > > >
> > > 
> > >
> 
> 
0
glenton1 (9)
9/30/2006 9:11:02 AM
duh...... it now works perfectly!

Really appreciate your help.


glenton wrote:
> Hi
>
> Make it
> MyCol1 = 11
> MyCol2 = 12
> MyCol3 = 13
>
> rather than K,L & M (i.e. the column number, rather than the column label.)
>
> Regards
> --
> Glenton
> www.leviqqio.com
> Quality financial modelling
>
>
> "shazmar" wrote:
>
> > Hi,
> >
> > Many thanks for your help.
> >
> > I'm getting an application-defined or object-defined error "1004" at
> > the following
> >
> > Do While MySh1.Cells(MyRow1, MyCol2) <> ""
> >
> > I've set up as follows - please see the *NOTES* I've put in
> >
> > Sub MyTransfer()
> >
> > Dim MySh1 As Worksheet
> > Dim MySh2 As Worksheet
> > Dim MyCol1 As Integer
> > Dim MyCol2 As Integer
> > Dim MyCol3 As Integer
> > Dim MyRow1 As Integer
> > Dim MyRow2 As Integer
> >
> >
> > Set MySh1 = Sheets("WindRow_Control")
> > Set MySh2 = Sheets("Windrow_Turns")
> >
> > MyCol1 = K 'column in Sheet1 where description is *NOTE* I don't need
> > this column but inserted it for this exercise
> > MyCol2 = l 'column in Sheet1 where allocation name is *NOTE* - vba
> > editor keeps changing this L to lower case l - is this the problem?
> >
> > MyCol3 = M 'extra column in Sheet1 to keep track of what information
> > has
> > 'been transferred to Sheet2
> > MyRow1 = 11 'row in Sheet1 where data starts
> > MyRow2 = 13 'row in Sheet2 where allocation headings are
> >
> >
> > 'Run a loop through all the rows of sheet1
> > Do While MySh1.Cells(MyRow1, MyCol2) <> ""
> >
> >
> >
> > glenton wrote:
> > > Hi
> > >
> > > I'm still not 100% sure that I've got it, but please try the following (& as
> > > always, pls save before running any macro).
> > >
> > > In brief what I've done is added a column in Sheet1 which keeps track of
> > > whether the information has been transferred to Sheet2 or not.
> > >
> > > You'll need to set the MyCol1, MyCol2, MyCol3, MyRow1, MyRow2 parameters in
> > > the macro (there are comments there to help you do that).  MyCol3 will simply
> > > be the column number of the first free column you have in Sheet1.  When
> > > entering data simply leave this blank.  The macro will change it to TRUE when
> > > it's copied that data across to Sheet2
> > >
> > > I recommend stepping through the macro (with F8) to get a feel for what it's
> > > doing.
> > >
> > > Sub MyTransfer()
> > >
> > > Dim MySh1 As Worksheet
> > > Dim MySh2 As Worksheet
> > > Dim MyCol1 As Integer
> > > Dim MyCol2 As Integer
> > > Dim MyCol3 As Integer
> > >
> > > Set MySh1 = Sheets("Sheet1")
> > > Set MySh2 = Sheets("Sheet2")
> > > MyCol1 = 1 'column in Sheet1 where description is
> > > MyCol2 = 2 'column in Sheet1 where allocation name is
> > > MyCol3 = 3 'extra column in Sheet1 to keep track of what information has
> > > been transferred to Sheet2
> > > MyRow1 = 4 'row in Sheet1 where data starts
> > > MyRow2 = 3 'row in Sheet2 where allocation headings are
> > >
> > > 'Run a loop through all the rows of sheet1
> > > Do While MySh1.Cells(MyRow1, MyCol2) <> ""
> > >
> > > 'Check if data has already been transferred to sheet2
> > > If Not MySh1.Cells(MyRow1, MyCol3) Then
> > >
> > > 'Check if Allocation name has already been used
> > > MyAllName = MySh1.Cells(MyRow1, MyCol2)
> > > Set MyAll = MySh2.Range("a1:iv1").Offset(MyRow2 - 1, 0).Find(MyAllName)
> > > If MyAll Is Nothing Then
> > > j = 1
> > > Do Until MySh2.Cells(MyRow2, j) = ""
> > > j = j + 1
> > > If j > 255 Then
> > > MsgBox "No more columns for allocations!"
> > > Exit Sub
> > > End If
> > > Loop
> > > MySh2.Cells(MyRow2, j) = MySh1.Cells(MyRow1, MyCol2)
> > > i = MyRow2 + 1
> > > Else
> > > j = MyAll.Column
> > > i = MyRow2 + 1
> > > Do Until MySh2.Cells(i, j) = ""
> > > i = i + 1
> > > If i > 50000 Then
> > > MsgBox "No more rows for allocation called " & MySh1.Cells(MyRow1, MyCol2)
> > > Exit Sub
> > > End If
> > > Loop
> > > End If
> > > MySh2.Cells(i, j) = MySh1.Cells(MyRow1, MyCol1)
> > > MySh1.Cells(MyRow1, MyCol3) = True
> > >
> > > End If
> > > MyRow1 = MyRow1 + 1
> > >
> > > Loop
> > >
> > > End Sub
> > >
> > > I hope this helps
> > >
> > > --
> > > Glenton
> > > www.leviqqio.com
> > > Quality financial modelling
> > >
> > >
> > > "shazmar" wrote:
> > >
> > > > Ok - sorry yes I guess it isn't very clear. I'll try again.
> > > >
> > > > Delivery x are the deliveries of manure, sawdust coir etc which come in
> > > > separately and in varying quantities. These "ingredients" are all put
> > > > together into a "windrow" - a huge pile which composts down. The
> > > > individual deliveries are recorded and each one is allocated to a
> > > > windrow.
> > > >
> > > > What I need to do on sheet 1 is record the allocation name against each
> > > > delivery (there may be any number - hence I put allocation A twice and
> > > > allocation C three times). Sheet 2 records all activities (i.e. turns)
> > > > on each windrow (which by this time is now being recorded as one unit).
> > > > So the allocation name has to automatically transfer over to sheet 2
> > > > and be inserted in the next availble column in a certain row. All data
> > > > relating to this windrow is then captured in that column. Council
> > > > requirements need each windrow to be back tracked to each raw material.
> > > >
> > > > I had thought with vba something like iterating through each allocation
> > > > name with the last "block" then being recognised as the entry for the
> > > > next free spot on the row. However my vba skills aren't up to that.
> > > >
> > > > It seems like the kind of thing that might often be required but I
> > > > can't seem to find a way to do it. Unfortunately because of the unknown
> > > > quantity of the deliveries and varying size of windrows I can't pre
> > > > allocate - it has to remain dynamic.
> > > >
> > > > Hope this is clearer.
> > > >
> > > >
> > > > glenton wrote:
> > > > > Hi
> > > > >
> > > > > It's not terribly clear what you want.  Could you perhaps clarify?
> > > > >  - Are the delivery x entries all identical?
> > > > >  - Do you want spreadsheet 2 literally to say "Row 13"?  What does this
> > > > > refer to?
> > > > >  - When you say automatically update, can you explain how it would be used?
> > > > > Someone enters what, where and how.  Then what happens and where?
> > > > >  - What are you trying to achieve? - there may be other ways of doing this.
> > > > >
> > > > > Sorry not to be of any help.
> > > > >
> > > > > Glenton
> > > > >
> > > > >
> > > > > "shazmar" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > Worksheet 1
> > > > > > Column B           Column K
> > > > > >
> > > > > > Delivery x                 Allocation A
> > > > > > Delivery x                 Allocation A
> > > > > > Delivery x                 Allocation B
> > > > > > Delivery x                 Allocation C
> > > > > > Delivery x                 Allocation C
> > > > > > Delivery x                Allocation C
> > > > > > Delivery x                Allocation D
> > > > > >
> > > > > > Worksheet 2
> > > > > >                COL 1          Col 2             Col 3              Col
> > > > > > 4
> > > > > > ROW 13   Allocation A  Allocation B  Allocation C    Allocation D
> > > > > >
> > > > > > I need to be able to automatically insert the allocation name in wks2
> > > > > > as it is added in wks1. The allocations cannot be pre named because one
> > > > > > or more labels is needed depending on the volume of the deliveries.
> > > > > > Once the volume is complete it becomes a "single unit" that retains the
> > > > > > same label name.
> > > > > >
> > > > > > I have looked at Vlookup, index & match plus some vba using last cell
> > > > > > but can't seem to get to a solution.
> > > > > >
> > > > > > Any help appreciated.
> > > > > > 
> > > > > >
> > > > 
> > > >
> > 
> >

0
9/30/2006 4:14:50 PM
Reply:

Similar Artilces:

Tab Name Change Error .xls] inserted
I have done something to my workbook and don't know how I did it. Everytime I add a new worksheet or rename an existing worksheet, Excel automatically puts ".xls]" in front of the name on the tab. When I attempt to delete .xls] it says that I have now entered an invalid name. Can anyone tell me what I did and how to fix it? Thank you for your help. Close that workbook and go to windows explorer. Traverse to the folder that holds the file. Rename that file so that it doesn't include any [ or ] characters. (Those characters confuse excel.) Alayna wrote: > > I h...

Lookup help #2
Hi All Trying to basically find - if "reading" A1 on sheet 1 then find "reading" on sheet 2 in column A and return cell No On worksheet 1 in cell A1 = Reading Worksheet 2 in column A = list of teams including Reading (Reading is eg A7 on this list) Result A7 Not having much joy - I must be looking at it the wrong way. Any help greatly welcome Thanks Paul Since you already know that Column A contains your data, would the row number be good enough for you? =MATCH(A1,Sheet2!A:A,0) -- HTH, RD ============================================== Please keep all corr...

v lookup
please help with instructions for dummies using v lookup. I have data from row 130 to 8000 and across 48 colums. I need all the columns data to be picked up. I have row 2 to 129 that I want the data to end up in. I enter in b2 vlookup(a130,b130:aw8000,130.0) It will not work and I have only just discovered v lookup and don't know what i am doing. Don't know if I quite follow exactly what you're trying to do. Try this formula in B2: =VLOOKUP($A130,$B$130:$AW$8000,COLUMNS($A:A),0) Copy it across to AW2, Then copy down to Row 129. -- HTH, RD -------------------------------...

SUMPRODUCT Using Unary on Filtered Range
I have a filtered range which l sum using the SUBTOTAL(9, A1:A100 formula. It allows me to sum the filtered range without bringing i the data that is not included in the filtered range. I want to do SUMPRODUCT on this filtered range. Normally I would use the followin SUMPRODUCT(A1:A100, B1:B100). However this will not work on a filtere range. How can I do this in one formula without creating a ne column? Thank -- Message posted from http://www.ExcelForum.com Hi try SUMPRODUCT(A1:A100, B1:B100,SUTOTAL(3,OFFSET($A$1,ROW($A$1:$A$100)-1,0))) -- Regards Frank Kabel Frankfurt, Germany &...

Subquery for column names
I have a database table with a large amount of analytical data in it - all floats but for a timestamp column. Instead of returning the entire set, I want to be able to return specific columns based on a "system" number that is specified as a parameter (in this case it is written in as "S02" for testing purposes) and compare the provided system number with the column names within the table - which is what the subquery currently does correctly. Aside from creating a stored procedure to handle the result and reformat it into a string to then use as a parameter of a ...

linking two cells from two different worksheets
This may sound like a basic question, but I can't figure out how to mak it so when you enter information into cell A1 on worksheet 1, i populates into cell A1 in worksheet 2(when it is blank), AND that cel A1 in worksheet 2 (when data is filled in) will populate into cell A of worksheet 1(when it is blank). Is this a circular reference issue? New lines are constantly being added to this file from two differen sources, so the range will automatically have to change as well.... I' sure that this is possible, and can be set up for the entire column i each respective sheet. Any help woul...

Executable Name of Windows Application
How do I retrieve the executable name (along with path) of a windows application using the Win32 API? Raj Well, if the application has a file extension associated with it, you could use AssocQueryString(). Otherwise, I'm not sure. "Marathoner" <rajk2000@msn.com.invalid> wrote in message news:OrM5C$KSEHA.1348@TK2MSFTNGP12.phx.gbl... > How do I retrieve the executable name (along with path) of a windows > application using the Win32 API? > > Raj > > I solved the problem. The following line of code solves the problem. ::GetModuleFileName(::GetModul...

Seeking help for total worksheet protection
The code below cycles through every worksheet and protects it. It then sets the active worksheet back to the first worksheet. For Each WkSht In m_DocExcel.Worksheets WkSht.Protect "mypassword", True, True, True, True WkSht.Activate m_AppExcel.ActiveWindow.DisplayGridlines = False m_AppExcel.ActiveWindow.DisplayHeadings = False Next WkSht m_DocExcel.Worksheets(1).Activate Is it possible to set protection on the entire workbook in one atomic operation, or do I need to set the protection for each sheet separately, as I am doing? And is th...

column names
Another user of an excel sheet I created has changed the column names from the alphabetic ones to numbers making the transfer of cells by thier ref. difficultt . Instead of reading ref A1 for example it shows R1C1. Any ideas how i can change it back - have searched help with no success Go into Tools/Options/General and uncheck the R1C1 reference style box Best rgds Chris Lav "genie" <genie@discussions.microsoft.com> wrote in message news:A88EC58E-39A2-420F-B6D9-D6E412B65DFC@microsoft.com... > Another user of an excel sheet I created has changed the column names fro...

"Lookup problem"
I have three columns. A and B is text and C is numbers. I use =SUMPRODUCT((A1:A70="Txt1")*(B1:B70="Txt2")*(C1:C70)) to find the value of C, where both a criteria in A and B is OK. This works perfectly all right. Now the challenge. The combination of Txt1 and Txt2 can be found in more rows than one. Therefore the SUMPRODUCT adds them up, as supposed to. What im interested in though is findes not the sum, but the smallest C value, that meets the criteria in A and B columns.I have tried =SUMPRODUCT((A1:A70="Txt1")*(B1:B70="Txt2")*MIN(C1:C70)) but ...

Problem with using named ranges to create charts
In excel 2003 to Define Name I write Names in workbook: Values Refers to: =Sheet1!$B$7:$C$7-Sheet1!$B$3:$C$3 and to SERIES formula I write =SERIES(;;Book1!Values;1) It works perfect in excel 2003. But why it doesn't work in excel 2007? Thanks ...

Ranges within Conditional Formatting
Within a cell, thisis basically what I want to do but am having trouble: if the value is between 95-100 background = green else if value is between (90-95) or between (100-105) background = yellow else if value is <90 OR >105) background = red Any ideas? Ryno wrote: > Within a cell, thisis basically what I want to do but am having > trouble: if the value is between 95-100 background = green > else if value is between (90-95) or between (100-105) background > = yellow else if value is <90 OR >105) background = red > > Any ideas? If you...

Friendly Name second request
All I have a need to change how Exchange sends email using friendly names. I know you can disable friendly names, but, I would like to know if there is a way, via AD or somewhere else, that you can modify what exchange uses as the attributes for friendly names. Right now it uses FirstName LastName, could or is there a way we could use say: Custom Attribute #2 ?? The reason I ask this is because, the internal corporate names for some users are not what they want others to read, like full last names for example, others don’t care I need a way to make this happen. Sending only email addresses ...

Max number of consectutive blanks in a range
I have this formula in column G =IF(SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N$700)>0,SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N$700),"") In Column H, I like to count the maximum number of consecutive blanks in that entire range. Meaning, there may 5 consective blanks, then 3, then 18, then 7. I'd like 18 to be returned. Thanks, Steve In H1 enter1 In H2 enter: =IF(G2="",H1+1,0) and copy down In another cell the answer is: =MAX(H:H) -- Gary''s Student - gsnu201001 "Steve" wrote: > I have this formula in col...

How do insert a watermark in an excel worksheet
I am preparing draft financial statements in Excel and i want them to have a watermark that says "Draft" on each page when i print them Look here: http://xldynamic.com/source/xld.xlFAQ0005.html -- Kind regards, Niek Otten Microsoft MVP - Excel "Ozias" <Ozias@discussions.microsoft.com> wrote in message news:5E008017-96A2-4AAB-B6F7-C906793F671A@microsoft.com... |I am preparing draft financial statements in Excel and i want them to have a | watermark that says "Draft" on each page when i print them ...

How to copy headers/footers across worksheets
Can I copy headers and footers from one worksheet to another in a workbook without copying the entire sheet? Bruce Select the sheet with the header/footer you wnat. Right-click and "Select all sheets". File>Page Setup>Headers/Footers and OK your way out. All sheets will have that setup. DO NOT FORGET to ungroup the sheets after you're done. What is being done to one will be done to all. Could make for some interesting changes<g> Gord Dibben Excel MVP On Tue, 11 Jan 2005 13:19:05 -0800, BruceD <BruceD@discussions.microsoft.com> wrote: >Can I copy ...

Excel (Office XP) secretly inserts personal metadata when closing documents without saving
Because I actually like to know and control when my documents are modified, I maintain digital checksums of all my documents (as anyone should to detect corruption or unauthorized tampering). Yesterday I was sent a binomial distribution table in an Excel document. I opened the document and then closed it without making any changes. The file's last-modified timestamp was unchanged. However, I detected modification of the file by a change in its SHA1 signature. A binary comparison between the original document revealed some minor differences, such as the original author's nam...

Protecting a Range of Specific Cell/s
Anyone knows how to protect a Range of specific cell/s By default, all cells are protected if you protect the worksheet, so select the whole worksheet, Format/ Cells/ Protection, and unlock, then select your range of cells, and Format/ Cells/ Protection, and lock, then you can protect your worksheet. -- David Biddulph "Blacksmith" <lalexander@bsiinsurance.com> wrote in message news:egqs$wL6HHA.1052@TK2MSFTNGP05.phx.gbl... > Anyone knows how to protect a Range of specific cell/s > ...

dynamic range? help!
tab1 salesperson 1/31/2007 2/28/2007 3/31/2007 XYZ ? ? ? tab2 salesperson date cont value XYZ 1/15/2007 500 XYZ 1/31/2007 1000 XYZ 1/10/2007 850 trying to setup a sumproduct to return XYZ total contract value for January (that obviously spans over multiple dates). can you assist me w/ a formula solution to this problem? thanks!! Kyle try this idea =sumproduct((month(b2:b22)=1)*(a2:a22="xyz")*c2:c22) -- ...

Changing the default number of visible rows and columns in a worksheet
By default, a workbook contains more colums and rows than one would eve need, how do I ensure that only a specified number fo rows and column are visible to the user? For now, I try and select all the columns that I dont want and all th rows that I odnt wnat and then use *hide*. Problems are: 1)Is this the only way to make a worksheet look neat and tidy? 2)Is there a better way of doing this so that I dont have to selec right until the 65536'th row and the 255th column?If so what is it? Thanks so muc -- Message posted from http://www.ExcelForum.com The number of rows and columns in ...

SUM across named sheets
I'm trying to get a sum of cell ranges from (named) sheets. For example I have sheets named for the week days: Sunday Monday Tuesday Wednesday Thursday Friday Saturday In each sheet I have employees and hours. I would like to do a SUM of all hours worked for an employee in all the sheets. In OpenOffice I can reference a cell in a different sheet: Sunday.A1 My question, how do I refer to a cell in a different (named sheet) in Excel? I'm thinking something like =SUM(Monday.B7:B45,Tuesday.b7:b45,Wednesday.b7:b45,Thursday.b7:b45,Friday.b7:b45,Saturday.b7:b45,Sunday.b7:b45) would wo...

When run the paramaeter query it shows #name
Hello, When I run find form ( a parameter qeuery), it opens the form for the record, but for some fields it shows #Name. Can anyone help me what goes wrong?, I have check the control, nothing wrong with the name of the control source With many thanks in advance -- H. Frank Situmorang On Tue, 22 Jan 2008 19:01:05 -0800, Frank Situmorang <hfsitumo2001@yahoo.com> wrote: >Hello, > >When I run find form ( a parameter qeuery), it opens the form for the >record, but for some fields it shows #Name. > >Can anyone help me what goes wrong?, I have check the control, noth...

Worksheet
Hi - have a single worksheet on which I can use Custom Views to show/hide different columns etc as I need. However, can I save a Sort order with each View as well as each custom view will have its own sort order and appearance - it's going to be a pain if I have to change the custom view then do a sort every time - is there a way round it - or am I stuck with changing the custom view and maybe assigning a button on the ribbon for a macro to sort for each view??? I think you're going to have to supply a macro for the sort. As for me, I'd put all the settings that th...

Getting rid of unused portions of worksheets
I have a workbook comprised of several sheets. I have formulas that I am entering into one or two cells in columns C or D, then copying the formatting to the rest of the cells in the same row, up to AK or AL. I want to know if it's possible to set up an 'end column' so when I drag a cell, or want to highlight a series of cells, the sheet stops when I get to the end of the active cells, instead of flying past it out into hundreds of columns I'm not using. It's not a huge deal - it would just make setting up these formulas a whole lot easier and quicker if I can copy and f...

Changing the name in the from field
I have set up a distribution list and would like to have just the name of my orgination to show in the From field when items are sent out and not my personal name and e-mail address. Is there a way to do this? Thanks Allen Thanks Tom, but I do not see what your asking me to look for. I'm using Outlook XP and when I look in the e-mail accounts (I think that's what your asking me to do) I find no 'General tab', no 'Properties' for my mail profile. So I'm still at a loss at what to do. I do want to than you for your help. "Tom - www.tditcorp.com" wr...