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

Little VBA Help

M

Meedan

New Member
I have NIL knowledge regarding VBA. Require help for below.


http://chandoo.org/wp/2011/04/07/show-details-on-demand-in-excel/


After reading above post of Chandoo, I am trying to modify the below Selection macro code.


In chandoo's sample excelsheet given in above link, when you select on items list, values in E28 change from 1 to 9.

I have data from D10:H17 & want these numbers to continue till 40.(D10:H17 contains 40 cells)

Like if I select D10 then 1 till D17 this numbers will go 8 & now when I select E10 number should update as 9.


I hope this post make sense.


Macro Code given in chandoo's sheet-


Sub UpdateAfterAction()

Dim topRow As Integer


topRow = Range("rngReviews").Cells(1, 1).Row

[valSelItem] = ActiveCell.Row() - topRow + 1

End Sub
 
Meedan,


Firstly welcome to the Chandoo.org forums and thanx for assistance in answering a few posts


Your problem isn't a VBA thing, well it sort of is.


Chandoo's code look at the active cell and compares it's address to the "rngReviews" Named Range. If they intersect, he runs another routine "UpdateAfterAction".


You need to change the range that is being checked

So goto the Formulas, Name Manager and select the rngReviews Range

then edit the area to suit your data
 
Thanks a ton for your reply, Hui.


I had tried changing the range i.e rngReviews but in vain.


The difference/problem in chandoo's code is irrespective of what you select C5 or D5 or E5 it will update cell value as 1 in E28.(If you can please have a look at the excel attached in chandoo's post.)


whereas what I am looking for is if I select C5 it should be 1, if D5 then 10 (as C5:C13 contains 9 cells) & IF E5 then 19, E6 would be 20 .... & so E13 should be 27.


Any more help would be much appreciated.
 
What is the rest of this relationship?

[pre]
Code: 
4	C	D	E
5	1	10	19
6			20
7
8
9
10
[/pre]

Or can you explain where the values come from some other way?
 
4 C D E

5 1 10 19

6 2 11 20

7 3 12 21

8 4 13 22

9 5 14 23

10 6 15 24

11 7 16 25

12 8 17 26

13 9 18 27

.

.

28 (Value to be updated here i.e E28)


As you can see above, if I select C5 then in E28 value should be 1.

If C6 then 2

If C7 then 3


Similarly, If I Select D5 then in E28 value should be 10.

If D6 then 11

If D7 then 12


Note: C5:E13 will not contain data from 1 till 27, they will have other data. Value in E28 should change from 1 to 27 depending upon my selection of cell.


Thanks for your support, HUI. I thought this might be easy so wrote Little VBA help.
 
Change the following subroutine as shown

[pre]
Code: 
Sub UpdateAfterAction()
Dim Col As Integer, Numb As Integer

Col = (ActiveCell.Column Mod 3) * 9 + 1
Numb = ActiveCell.Row - 5 + Col

[valSelItem] = Numb

End Sub
[/pre]
 
Thank you so much for your support & guidance, HUI.


The above code works perfectly.


Just made a little change in the last line have added - 1 after Numb.
 
Sorry to bother you again HUI.


The code given by you works perfectly if my data has 4 columns, but if I add 5th column & change rngreviews range, for 5th COlumn it again starts with 1. I almost spent an hour doing R&D as I thought I would be able to crack why this happens but in vain.


My Data is in D10 till H17. (D10:H17)


As my data is in D10:H17, have made below changes to the code.


-------------------------------------


Sub UpdateAfterAction()

Dim Col As Integer, Numb As Integer


Col = (ActiveCell.Column Mod 4) * 8 + 1

Numb = ActiveCell.Row - 10 + Col


[valSelItem] = Numb


End Sub
 
[pre]
Code: 
Sub UpdateAfterAction()
Dim Col As Integer, Numb As Integer
Col = (ActiveCell.Column Mod 4) * 9 + 1
Numb = ActiveCell.Row - 5 + Col
[valSelItem] = Numb
End Sub
[/pre]
 
[pre]
Code: 
Sub UpdateAfterAction()
Dim Col As Integer, Numb As Integer

Col = (ActiveCell.Column - 4 Mod 5) * 9 + 1
Numb = ActiveCell.Row - 5 + Col

[valSelItem] = Numb

End Sub
[/pre]

Also adjust the Named Range rngReviews and make sure it is

='Rating Summary'!$D$5:$H$13
 
[pre]
Code: 
Sub UpdateAfterAction()
Dim Col As Integer, Numb As Integer

Col = (ActiveCell.Column - 4 Mod 5) * 9 + 1
Numb = ActiveCell.Row - 10 + Col

[valSelItem] = Numb

End Sub
[/pre]

and adjust Named Range rngReviews

='Rating Summary'!$D$5:$H$13
 
Adding - 4 in that line works perfectly.


I cant understand the logic behind this, but it works. Great !


Thanks once again.
 
Meedan

You want the first value to be a 0

so the first Mod has to be of the number of Columns wide the area is

But the first Column is D (4) so subtract 4

0 mod 5 is 0

1 mod 5 is 1 etc

Then Multiply each by 9

0 x 9 = 0

1 x 9 = 9

Then add 1 to get it right


Then add Row Number to increment by 1 for each row
 
You must log in or register to reply here.
Back
Top