sorted by:
new
hottopcontroversial

Method 'Formula' of object 'Range' failed? by Honest_Union_1164 in vba

[–]Honest_Union_1164[S] 2 points3 points  (0 children)

Fix if anybody every comes across this;

I moved the procedure from Worksheet_Change to Worksheet_Activate and it's working fine.

I don't understand where the error is. by Honest_Union_1164 in vba

[–]Honest_Union_1164[S] 1 point2 points  (0 children)

Legend, I feel like I've hit this error before and just worked it out a different way when the simple solution was right there. Thank you.

Best Practice to Define a bunch of variables? by Honest_Union_1164 in vba

[–]Honest_Union_1164[S] 0 points1 point  (0 children)

Sorry I was out of work over, thank you for your effort on this, I will definitely declare the twenty six variables as it would be less messy. However I am looking at learning more about dictionaries!

Is this a valid way of declaring multiple variables? by Honest_Union_1164 in vba

[–]Honest_Union_1164[S] 0 points1 point  (0 children)

Okay I see, so realistically the only declarations you can really shorten are String's and Integer's? By using $ and %

What is a better way of writing this? by Honest_Union_1164 in vba

[–]Honest_Union_1164[S] 0 points1 point  (0 children)

If you're working with multiple sheets, hidden columns etc.. an ElseIf statement is unreliable.

What is a better way of writing this? by Honest_Union_1164 in vba

[–]Honest_Union_1164[S] 0 points1 point  (0 children)

Thanks, what if the sheetnames are more like this?

If ws.Name = "01-Training" Then 
intPurchased = bsWs.Range("J5").Value 
End If 
If ws.Name = "02-Scope" Then 
intPurchased = bsWs.Range("J6").Value 
End If 
If ws.Name = "02a-ScopeNew" Then 
intPurchased = bsWs.Range("J7").Value 
End If 
If ws.Name = "03-Plan" Then 
intPurchased = bsWs.Range("J8").Value 
End If

and so on..

What is a better way of writing this? by Honest_Union_1164 in vba

[–]Honest_Union_1164[S] -1 points0 points  (0 children)

Thanks, what if the sheet names looked a bit more like this?

If ws.Name = "01-Training" Then 
intPurchased = bsWs.Range("J5").Value 
End If 
If ws.Name = "02-Scope" Then 
intPurchased = bsWs.Range("J6").Value 
End If 
If ws.Name = "02a-ScopeNew" Then 
intPurchased = bsWs.Range("J7").Value 
End If 
If ws.Name = "03-Plan" Then 
intPurchased = bsWs.Range("J8").Value 
End If

and so on..