|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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.
|
|
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:
[Quoted Text] > 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.
>
>
|
|
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:
[Quoted Text] > 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.
> >
> >
|
|
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:
[Quoted Text] > 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.
> > >
> > >
>
>
|
|
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:
[Quoted Text] > 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.
> > > >
> > > >
> >
> >
|
|
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:
[Quoted Text] > 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.
> > > > >
> > > > >
> > >
> > >
>
>
|
|
duh...... it now works perfectly!
Really appreciate your help.
glenton wrote:
[Quoted Text] > 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.
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >
|
|
|