Help MSB (microsoft visual basic) code for an Excel Macro button

[Closed]
Report
Posts
1
Registration date
Tuesday August 18, 2015
Status
Member
Last seen
August 18, 2015
-
 RayH -
Hello,
I am trying to create a macro to help the input in a complex excel file.

I have created several dropdown menues to choose from a list:
for names: A,B,C,D (which should refer to different excel sheets)
for jobs: Job1, Job2, Job3 (which choose from the same area in all sheets, lets say A10:B13)
for job titles: X,Y,Z (which chooses only 1 cell in that Job area)
The last cell must contain a numerical value for example:
Your age: ___
I am trying to create a macro button that takes the Age value and writes in it the Name sheet in the Job area in the Job Title cell.

I would be very thankful for a code that does this.

2 replies

Posts
2819
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 25, 2021
487
Hi kristiyanstoychev,

Could you upload your file without any sensitive information to a free file sharing site like www.speedyshare.com or ge.tt and then post back the download link, for better understanding of your query?

Best regards,
Trowa
Hey Trowa, I've uploaded the file here:
https://www.dropbox.com/s/1wzed9nzhsw0amf/Sample.xlsx?dl=0
It is obviously simplified but this will be enough to show me the code that I need to write for my job. Thank you.
I had bit of fun with this:


=OFFSET(INDIRECT($B$3 &"!A1",TRUE),MATCH(Input!$B$4,INDIRECT($B$3 &"!A:A",TRUE),0)+MATCH($B$6,INDIRECT($B$3&"!A"&MATCH(Input!$B$4,INDIRECT($B$3 &"!A:A",TRUE),0)+2&":A"&MATCH(Input!$B$4,INDIRECT($B$3 &"!A:A",TRUE),0)+5,TRUE),0),MATCH($B$5,INDIRECT($B$3 &"!B" & MATCH(Input!$B$4,INDIRECT($B$3 &"!A:A",TRUE),0)+1 & ":D" & MATCH(Input!$B$4,INDIRECT($B$3 &"!A:A",TRUE),0)+1,TRUE),0))


Paste this into the "How Many?" field B7

In order for this to work the references for AGE have to be the same in all instances. They were different in your sample. That is to say that the Validations need to match what is in the Company tabs.
that seems helpful. In this way I can take a value from a table and show it in a cell. What I want to do though, is take that value from the cell and write it in a specific place in the table.
Example - you have a table (A1:B5) with empty cells. With this function I need to take the value from cell H10 and write it in (A3, which would be determined by those offsets)
Do you know the function for this mate?
Thanks
Sorry, you will need to provide another sample with "before and after" examples.