Hi,
The problem that I can not seem to overcome is that I have a script that has a loop structure that needs to be re-written.
I did not find a way to enclose screen prints or files that could serve to illustrate the structure I got now, and also what the new structure would look like. Hence I will try to describe how it looks in the spreadsheet before I move on to the scripting.
The initial structure that I got now in Excel is the following:
'Main list':
Cell F2: Name3 (read: a company name)
Cell F3: Name1
Cell F4: Name4
Cell F5: Name5
Cell F6: Name2
Cell H2: a from-date
Cell I2: a to-date
Column G: in this column values from the reference list will be put next to the corresponding name, filtered by dates in cells H2 and I2.
'Reference list':
Cell A2: Name1 (read: similar to Name1 in the 'main-list')
Cell A3: Name2
Cell A4: Name1
Cell A5: Name 2
Cell B2: 30.06.2006 (read: from-dates)
Cell B3: 30.06.2006
Cell B4: 20.08.2006
Cell B5: 30.08.2006
Cell C2: 30.08.2006 (read: to-dates)
Cell C3: 30.08.2006
Cell C4: 30.08.2006
Cell C5: 30.09.2006
Cell D2: 500 000
Cell D3: 9 700 000
Cell D4: 2 100 000
Cell D5: 9 700 100
The script I got now will draw values from the 'reference list' and put them into the 'main list' next to the corresponding name, filtered by the two dates (which is given in cells H2 and I2.
As of now the 'main-list' and the 'reference-list' are on the same worksheet, and for this setup I have used the following script:
Sub Period()
InSheet1_FromDate = Range("h2").Value
InSheet1_ToDate = Range("i2").Value
InSheet1_Company = Range("f2").Value
InSheet1_CompanyCellAdress
= Range("f2").Address
Range("f65536").Select
Selection.End(xlUp).Select
InSheet1_LastCompanyCellAd
ress = Selection.Offset(1, 0).Address
Range("f1").Select
InSheet1_StartingPoint = Selection.Address
Range("a65536").Select
Selection.End(xlUp).Select
InSheet2_LastCompanyInRefe
renceList = Selection.Address
While InSheet1_CompanyCellAdress
<> InSheet1_LastCompanyCellAd
ress
Range(InSheet2_LastCompany
InReferenc
eList).Sel
ect
While InSheet2_CompanyInReferenc
eList <> "$A$1"
If Selection.Value = Range(InSheet1_CompanyCell
Adress).Va
lue Then
If Selection.Offset(0, 1).Value >= InSheet1_FromDate Then
If Selection.Offset(0, 2).Value <= InSheet1_ToDate Then
Range(InSheet1_CompanyCell
Adress).Of
fset(0, 1).Value = Selection.Offset(0, 3).Value
End If
End If
End If
Selection.Offset(-1, 0).Select
InSheet2_CompanyInReferenc
eList = Selection.Address
Wend
InSheet2_CompanyInReferenc
eList = Range(InSheet2_LastCompany
InReferenc
eList).Add
ress
InSheet1_CompanyCellAdress
= Range(InSheet1_CompanyCell
Adress).Of
fset(1, 0).Address
Wend
End Sub
(I guess by now I have revealed myself as a novice).
Now, in the new structure the 'reference-list' will maintain the cell references given but it will be placed on a different worksheet than the 'main-list'. In the script above I have indicated which worksheet the variables will refer to. 'InSheet1' refers to the sheet that contains the 'main-list', and 'InSheet2' refers to the worksheet in which the 'reference-list' will be placed.
Could anyone please tell me how to tweak/re-write the script into the new structure (in which the 'reference-list' is placed on a different worksheet , read: Sheet2)? I have given this question 500 points as this has troubled me for quite a while.
Thanks,
Wiley Powers
Start Free Trial
