• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

computed data falling into independent cells

F

fred

Member
I have a list of employee names in column A2 and down a few hundred rows in Sheet1. In Sheet2 I have a pulldown menu referencing the names in Sheet1. Depending on the name chosen, Sheet2 H18 will compute different performance number for different employees.


Question: Is there a solution to have Excel compute all the performance number and push that computed performance number back to sheet1, say column Z?


I'm trying to use the counter in VBA but I'm not getting the result right. Could anyone help please? Thank you very much.
 
Hi, fred!


I haven't checked it but... try this:

-----

[pre]
Code: 
Sub x()
' constants
Const ksWs1 = "Sheet1"
Const ksRng1 = "A:Z"
Const ksWs2 = "Sheet2"
Const ksRng2 = "H18"
' declarations
Dim I As Integer
Dim rng1 As Range, rng2 As Range
' start
Set rng1 = Worksheets(ksWs1).Range(ksRng1)
Set rng2 = Worksheets(ksWs2).Range(ksRng2)
' process
With rng1
For I = 2 To .Rows.Count
rng2.Value = .Cells(I, 1).Value
.Cells(I, 26).Value = rng2.Value
Next I
End With
' end
End Sub
[/pre]
-----


If any issue arises it should work.


Regards!
 
I got a message: Run-time error '9': Subscript out of range


on the line where


Set rng1 = Worksheets(ksWs1).Range(ksRng1)
 
Hi, fred!


Checked and updated, two errors: I should be long not integer cause of overflow over 32767, and missed drop-down cell selection in Sheet2 (assumed A1 for the example).


-----

[pre]
Code: 
Sub x()
' constants
Const ksWs1 = "Sheet1"
Const ksRng1 = "A:Z"
Const ksWs2 = "Sheet2"
Const ksRng2 = "H18"
Const ksRng3 = "A1"
' declarations
Dim I As Long
Dim rng1 As Range, rng2 As Range, rng3 As Range
' start
Set rng1 = Worksheets(ksWs1).Range(ksRng1)
Set rng2 = Worksheets(ksWs2).Range(ksRng2)
Set rng3 = Worksheets(ksWs2).Range(ksRng3)
' process
With rng1
For I = 2 To .Rows.Count
If .Cells(I, 1).Value = "" Then Exit For
rng3.Value = .Cells(I, 1).Value
.Cells(I, 26).Value = rng2.Value
Next I
End With
' end
End Sub
[/pre]
-----


Let's see now.


Regards!


PS: Here's the link to the witness file ;)

http://dl.dropbox.com/u/60558749/computed%20data%20falling%20into%20independent%20cells%20%28for%20fred%20at%20chandoo.org%29.xlsm
 
same error persisted on Set rng1 = Worksheets(ksWs1).Range(ksRng1)


Basically i was using the same file you sent me yesterday


http://dl.dropbox.com/u/60558749/How%20to%20write%20a%20macro%20to%20print%20data%20pertaining%20to%20a%20list%20box_%20-%20Testing%20%28for%20fred%20at%20chandoo.org%29.xlsm


I added a formula in H18 on the "calculator" page and the result would go back to column Z and fill out Z4 down on the "Input" page.
 
Hi, fred!

Gonna check your file right now, maybe you want to do the same with mine.

Back when done.

Regards!
 
Hi, fred!

Guess who is? Yeap, it's me again...

Well, firstly there's no formula in H18 so for testing purposes I added one that multiplies H17 by 2 (yes, I took the nearest possible cell so as to minimize the times or time I should press cursor keys, just effort economy).

And here I'm facing the second and real issue.

When would you want to update column Z in Input sheet from H18 in Calculator sheet? Because it's needed a process that cycles on all cells in range, so or you add it to the print selected process, or you create another button, or you run it manually once (but how do you update it regularly?)... so I began wondering "what might be this calculation that can't be performed with formulas in Input sheet if it can be performed in Calculator sheet in such way?". Can you tell me? Because if it may be calculated in Input sheet, the only problem will be reduced to add a reference in H18 on Calculator sheet.


The link to the same updated file:

http://dl.dropbox.com/u/60558749/How%20to%20write%20a%20macro%20to%20print%20data%20pertaining%20to%20a%20list%20box_%20-%20Testing%20%28for%20fred%20at%20chandoo.org%29.xlsm


Check row 63 (emp. No. 60) too.


Regards!
 
Hi SirJB7,


sorry about H18 has no formula.


I'd prefer column Z to run independent of the printing process because (may be) managers don't want to have all the hard copies and I may not want to print those reports without an employee name. As you can see this will only update once a quarter (or a month at most frequent).
 
Hi, fred!

Then download again the file, and format H18 with white ink :)

And use the purple button for updating Z column only.

Don't forget changing my H18 formula.

Regards!
 
WOW! My jaw's on the floor rolling down a basement. That is super awesome. I'll need to learn that VBA.


Thank you very much, all hail to SirJB7!!!
 
You must log in or register to reply here.
Back
Top