Lookup a range and insert range name into second worksheet

S

shazmar

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.
 
G

glenton

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
 
S

shazmar

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.
 
G

glenton

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
 
S

shazmar

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) <> ""
 
G

glenton

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
 
Top