Dear Friends,
I'm stuck up with interesting but challenging problem and am looking for some guidance on how to fix the problem. Pls allow me try to explain with best possible details.
I have 4 worksheets namely Buildings, Scoring, Costing and Report.
I need to capture user requirements, then match which of my products in raw data matches it. Once I identify it then I've to do Costing calculation and produce the Report as output.
Here's how I'm approching this coding situation.
My BUILDINGS worksheet acts as my raw data and list s different features of all the buildings separately.
Once I capture user requirements then I've to compare it with my raw data in BUILDINGS sheet and shorlist different buildings for my recommendations.
I shortlist this by using SCORING sheet which gives a score of 1 for every matched feature and 0 for unmatched feature against all buildings. Total Score in Row 20 in SCORING sheet helps identify qualified buildings. I've done this part already .For example total score of 8 for a building means it matches the user requirements. Here in my sample the Buildings 1, 6 and 14 are the shortlisted buildings..
These shortlisted buildings need to undergo COSTING sheet one by one.
The COSTING sheet has some embedded formulas from builder which needs some fields from User inputs and then applies the related costs for offering to customer. So my macro needs to run in a loop for Each Qualified Building . For any building, It pastes the user parameters in cells B6 to B8 (shown in GREY cells) . This will result in final costing values to appear in cells B 9 to B14. These Final values along with Building Name needs to be captured in REPORT worksheet.
So if I got THREE buildings as qualified (which matches score of 8) then my output in REPORT worksheet should show all those three buildings along with related costs.
I'm trying to do this in 3-parts.
Part 1 - Identify qualifying buildings. This is done.
Part 2 - Copy only shortlisted buildings along with their features at the bottom of SCORING sheet. Range B25 onwards. This is stuck with errors.
Part -3 Pick up only required parametrs and paste in Costing sheets. This part I need to figuer out best way to setup some Loops.
Problems I'm facing are in:
Part 2 :
Some of my codes run individually when I put them in different sheets and while those sheets are active. In real scenario I've to run them ffrom central module.
But I'm getting errors in referencing to different worksheets through my code in module. I want my code to go to some reference cell for example in Cell A1 in BUILDINGS and then proceed to OFFSET location for copying the required cells. I get this error whenever my code referes to another worksheet within same workbook.
"Run Time error 1004 Activate method of Range Class failed"
Part 3:
What's the best way to pick the required fields from inputs and retrieve the corresponding Costing outputs and paste it in REPORTS ?
Here's my Code:
Option Explicit
Sub ListingQualifiedBuildings()
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim wsBuildings As Worksheet, wsScoring As Worksheet, wsCosting As Worksheet, wsReport As Worksheet
Dim score As String
Dim counter As Long
Dim QualifyingScore As String
Set wsScoring = wb.Worksheets("Scoring")
wsScoring.Range("b20").Activate
'Declare & CORRECT the total qualifying score here
QualifyingScore = 8
Range("B20").Select
counter = 1
Do Until ActiveCell.Value = ""
Range("B20").Select ' required again as each offset is counting from B20
ActiveCell.Offset(0, counter).Select
'Setting limits for counter run
If ActiveCell.Value = "" Then
Exit Sub
Else
End If
score = ActiveCell.Value
If score = QualifyingScore Then
Call RangeSelection
Else
End If
counter = counter + 1
Loop
End Sub
Sub RangeSelection()
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim wsBuildings As Worksheet, wsScoring As Worksheet, wsCosting As Worksheet, wsReport As Worksheet
Dim countColumnLocation As Long
Set wsBuildings = wb.Worksheets("Buildings")
Set wsScoring = wb.Worksheets("Scoring")
'setting temporary counter to know how many columns to offset. It should get column location from qualifying building column in Scoring sheet
countColumnLocation = 2
wsBuildings.Range("a1").Offset(0, countColumnLocation).Activate
wsBuildings.Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
'Above copied selection should paste in Scoring sheet at location A25 and in its subsequent Righ columns.
'For example if qualified scores appear in column C,H,J in Scoring Sheet which reflects Building 1, 6 & 14.
'Then actual Building's features for Building 1,6 & 14 will be copied from Range B25,C25 and D25
wsScoring.Range("a25").Activate
Do Until ActiveCell.Value = ""
ActiveCell.Offset(0, 1).Select
Loop
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
' End With
End Sub
Excel Screens are here for ref...
Excel 2007
<tbody>
</tbody>
Scoring Sheet
Excel 2007
<tbody>
</tbody>
Costing Sheet
Excel 2007
<tbody>
</tbody>
Final Report
Excel 2007
<tbody>
</tbody>
I'm stuck up with interesting but challenging problem and am looking for some guidance on how to fix the problem. Pls allow me try to explain with best possible details.
I have 4 worksheets namely Buildings, Scoring, Costing and Report.
I need to capture user requirements, then match which of my products in raw data matches it. Once I identify it then I've to do Costing calculation and produce the Report as output.
Here's how I'm approching this coding situation.
My BUILDINGS worksheet acts as my raw data and list s different features of all the buildings separately.
Once I capture user requirements then I've to compare it with my raw data in BUILDINGS sheet and shorlist different buildings for my recommendations.
I shortlist this by using SCORING sheet which gives a score of 1 for every matched feature and 0 for unmatched feature against all buildings. Total Score in Row 20 in SCORING sheet helps identify qualified buildings. I've done this part already .For example total score of 8 for a building means it matches the user requirements. Here in my sample the Buildings 1, 6 and 14 are the shortlisted buildings..
These shortlisted buildings need to undergo COSTING sheet one by one.
The COSTING sheet has some embedded formulas from builder which needs some fields from User inputs and then applies the related costs for offering to customer. So my macro needs to run in a loop for Each Qualified Building . For any building, It pastes the user parameters in cells B6 to B8 (shown in GREY cells) . This will result in final costing values to appear in cells B 9 to B14. These Final values along with Building Name needs to be captured in REPORT worksheet.
So if I got THREE buildings as qualified (which matches score of 8) then my output in REPORT worksheet should show all those three buildings along with related costs.
I'm trying to do this in 3-parts.
Part 1 - Identify qualifying buildings. This is done.
Part 2 - Copy only shortlisted buildings along with their features at the bottom of SCORING sheet. Range B25 onwards. This is stuck with errors.
Part -3 Pick up only required parametrs and paste in Costing sheets. This part I need to figuer out best way to setup some Loops.
Problems I'm facing are in:
Part 2 :
Some of my codes run individually when I put them in different sheets and while those sheets are active. In real scenario I've to run them ffrom central module.
But I'm getting errors in referencing to different worksheets through my code in module. I want my code to go to some reference cell for example in Cell A1 in BUILDINGS and then proceed to OFFSET location for copying the required cells. I get this error whenever my code referes to another worksheet within same workbook.
"Run Time error 1004 Activate method of Range Class failed"
Part 3:
What's the best way to pick the required fields from inputs and retrieve the corresponding Costing outputs and paste it in REPORTS ?
Here's my Code:
Option Explicit
Sub ListingQualifiedBuildings()
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim wsBuildings As Worksheet, wsScoring As Worksheet, wsCosting As Worksheet, wsReport As Worksheet
Dim score As String
Dim counter As Long
Dim QualifyingScore As String
Set wsScoring = wb.Worksheets("Scoring")
wsScoring.Range("b20").Activate
'Declare & CORRECT the total qualifying score here
QualifyingScore = 8
Range("B20").Select
counter = 1
Do Until ActiveCell.Value = ""
Range("B20").Select ' required again as each offset is counting from B20
ActiveCell.Offset(0, counter).Select
'Setting limits for counter run
If ActiveCell.Value = "" Then
Exit Sub
Else
End If
score = ActiveCell.Value
If score = QualifyingScore Then
Call RangeSelection
Else
End If
counter = counter + 1
Loop
End Sub
Sub RangeSelection()
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim wsBuildings As Worksheet, wsScoring As Worksheet, wsCosting As Worksheet, wsReport As Worksheet
Dim countColumnLocation As Long
Set wsBuildings = wb.Worksheets("Buildings")
Set wsScoring = wb.Worksheets("Scoring")
'setting temporary counter to know how many columns to offset. It should get column location from qualifying building column in Scoring sheet
countColumnLocation = 2
wsBuildings.Range("a1").Offset(0, countColumnLocation).Activate
wsBuildings.Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
'Above copied selection should paste in Scoring sheet at location A25 and in its subsequent Righ columns.
'For example if qualified scores appear in column C,H,J in Scoring Sheet which reflects Building 1, 6 & 14.
'Then actual Building's features for Building 1,6 & 14 will be copied from Range B25,C25 and D25
wsScoring.Range("a25").Activate
Do Until ActiveCell.Value = ""
ActiveCell.Offset(0, 1).Select
Loop
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
' End With
End Sub
Excel Screens are here for ref...
Excel 2007
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Building Offer / Criteria | Building1 | Building2 | Building3 | Building4 | Building5 | Building6 | Building… | Building20 |
| 2 | Minimum Price (US$) | $2,000 | $3,000 | $4,000 | $5,000 | $1,500 | $2,500 | $3,500 | $4,000 |
| 3 | Maximum Price (US$) | $3,800 | $4,800 | $5,800 | $6,800 | $3,300 | $4,300 | $5,300 | $5,800 |
| 4 | Currency (USD, GBP, EUR, CNY) | USD | GBP | USD | EUR | CNY | USD | USD | USD |
| 5 | Urgency (Lo/Med/Hi/NA) | Lo | Med | Med | Med | Hi | Hi | Med | Med |
| 6 | Inclusion {Multiple Selections }( eg.. High Floor, Sea Facing, Security, Private Lawn, NA) | High Floor | Sea Facing | Private Lawn | High Floor | Security | Private Lawn | Sea Facing | |
| 7 | Exclusion {Multiple Selections } ( eg..Mountain Facing, No Lift, Security) | Mountain Facing | No Lift | Mountain Facing | No Lift | Mountain Facing | Mountain Facing | Mountain Facing | No Lift, Security |
| 8 | Sr Citizen Discount (Y/N) | Y | Y | Y | N | N | N | N | N |
| 9 | Protected Rental (Y/N) | Y | N | N | Y | Y | N | N | N |
| 10 | Minimum Contract (1,2,3,4 Yrs) | 1 | 2 | 3 | 3 | 4 | 2 | 1 | 1 |
| 11 | Security Deposit (20%, 30%, 40% depending on No. of Contract years) | 20% | 40% | 30% | 20% | 30% | 30% | 30% | 30% |
| 12 | Distance from Mall | 1 | 2 | NA | 1 | NA | 3 | NA | 2 |
| 13 | Metro station Proximity (Y/N) | Y | N | N | Y | Y | N | N | Y |
| 14 | Bus Stop Proximity | N | Y | Y | Y | N | Y | N | N |
| 15 | School Proximity | Y | N | Y | Y | Y | N | Y | N |
| 16 | Cinema Proximity (Y/N/NA) | NA | Y | N | NA | NA | N | N | N |
| 17 | Hospital (<1 Km, 1-3 KM, >5KM) | 1-3 KM | 1 KM | >5KM | 1 KM | >5KM | 1-3 KM | 1 KM | >5KM |
| 18 | Misc Building features | Shops | Cinema | Party Hall | |||||
| 19 | Misc Building features | Kindergarten | Shops | Shops | |||||
| 20 | Misc Building features | Dance Room | |||||||
| 21 | Misc Building features | Yoga | |||||||
| 22 | Misc Building features | Badminton | |||||||
| 23 | Misc Building features | Squash |
<tbody>
</tbody>
Buildings
Scoring Sheet
Excel 2007
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Ref Code | Building Offer / Criteria | Building1 | Building2 | Building3 | Building4 | Building5 | Building6 | Building…7 |
| 2 | 1 | Minimum Price (US$) | |||||||
| 3 | 2 | Maximum Price (US$) | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
| 4 | 3 | Currency (USD, GBP, EUR, CNY) | 0 | 0 | 1 | 0 | 1 | 1 | 1 |
| 5 | 4 | Risk Appetite (Lo/Med/Hi/NA) | 1 | 0 | 0 | 1 | 1 | 1 | 1 |
| 6 | 5 | Inclusion {Multiple Selections }( eg.. High Floor, Sea Facing, Security, Private Lawn, NA) | 1 | 0 | 1 | 1 | 1 | 1 | 0 |
| 7 | 6 | Exclusion {Multiple Selections } ( eg..Mountain Facing, No Lift, Security) | 0 | 0 | 1 | 1 | 1 | 1 | 1 |
| 8 | 7 | Sr Citizen Discount (Y/N) | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| 9 | 8 | Protected Rental (Y/N) | 1 | 0 | 0 | 1 | 0 | 1 | 1 |
| 10 | 9 | Minimum Contract (1,2,3,4 Yrs) | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
| 11 | 10 | Security Deposit (20%, 30%, 40% depending on No. of Contract years) | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| 12 | 11 | Distance from Mall | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 13 | 12 | Metro Station Proximity (Y/N) | 1 | 0 | 0 | 0 | 0 | 0 | 1 |
| 14 | 13 | Bus Stop Proximity | 1 | 0 | 0 | 0 | 1 | 1 | 0 |
| 15 | 14 | School Proximity | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 16 | 15 | Cinema Proximity (Y/N/NA) | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 17 | 16 | Hospital (<1 Km, 1-3 KM, >5KM) | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 18 | |||||||||
| 19 | |||||||||
| 20 | Total | 8 | 3 | 5 | 6 | 7 | 8 | 7 | |
| 21 | |||||||||
| 22 | |||||||||
| 23 | |||||||||
| 24 | |||||||||
| 25 | Building Offer / Criteria | Building1 | Building6 | Building…14 | |||||
| 26 | Minimum Price (US$) | $2,000 | 2500 | ||||||
| 27 | Maximum Price (US$) | $3,800 | 4300 | 0 | |||||
| 28 | Currency (USD, GBP, EUR, CNY) | USD | USD | 1 | |||||
| 29 | Urgency (Lo/Med/Hi/NA) | Lo | Hi | 1 | |||||
| 30 | Inclusion {Multiple Selections }( eg.. High Floor, Sea Facing, Security, Private Lawn, NA) | High Floor | 1 | ||||||
| 31 | Exclusion {Multiple Selections } ( eg..Mountain Facing, No Lift, Security) | Mountain Facing | Mountain Facing | 1 | |||||
| 32 | Sr Citizen Discount (Y/N) | Y | N | 1 | |||||
| 33 | Protected Rental (Y/N) | Y | N | 1 | |||||
| 34 | Minimum Contract (1,2,3,4 Yrs) | 1 | 2 | 0 | |||||
| 35 | Security Deposit (20%, 30%, 40% depending on No. of Contract years) | 20% | 0.3 | 1 | |||||
| 36 | Distance from Mall | 1 | 3 | 1 | |||||
| 37 | Station Proximity (Y/N) | Y | N | 0 | |||||
| 38 | Bus Stop Proximity | N | Y | 0 | |||||
| 39 | School Proximity | Y | N | 0 | |||||
| 40 | Cinema Proximity (Y/N/NA) | NA | N | 0 | |||||
| 41 | Hospital (<1 Km, 1-3 KM, >5KM) | 1-3 KM | 1-3 KM | 0 | |||||
| 42 | Misc Building features | ||||||||
| 43 | Misc Building features | ||||||||
| 44 | Misc Building features | ||||||||
| 45 | Misc Building features | ||||||||
| 46 | Misc Building features | ||||||||
| 47 | Misc Building features |
<tbody>
</tbody>
Scoring
Costing Sheet
Excel 2007
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 5 | Building Name (from qualified Building details) | Building 1 | |||
| 6 | Minimum Price (US$) (from qualified Building details) | 1500 | |||
| 7 | Metro station Proximity (Y/N) | Y | |||
| 8 | Inclusion {Multiple Selections }( eg.. High Floor, Sea Facing, Security, Private Lawn, NA) (from qualified Building details) | High Floor | |||
| 9 | Basic Cost {from Builder's Calculation Parameters) | 5500 | Fixed for building in B5 | ||
| 10 | Location Factor (%) {from Builder's Calculation Parameters) | 440 | Fixed for building location | ||
| 11 | Cost Overhead % Charges due to Inclusions {from Builder's Calculation Parameters) | 68.75 | Provided by builders (% of Basic Cost) | ||
| 12 | Fixed Maintenance Charges {from Builder's Calculation Parameters) | 550 | Fixed $ Amt | ||
| 13 | |||||
| 14 | Total | 6558.75 |
<tbody>
</tbody>
Costing
Final Report
Excel 2007
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Buildings Names | Building 1 | Building 6 | Building 10 |
| 2 | Basic Cost | 3500 | 3000 | 5500 |
| 3 | Location Factor (%) | 280 | 240 | 440 |
| 4 | Cost Overhead % Charges due to Inclusions | 43.75 | 37.5 | 68.75 |
| 5 | Fixed Maintenance Charges | 350 | 300 | 550 |
| 6 | ||||
| 7 | Total | 4173.75 | 3577.5 | 6558.75 |
<tbody>
</tbody>
Report
I just resolved part 2 also. Now I seek guidance on Part 3 of my problem pls..pls.pls...
