Microsoft Office

Microsoft Office Questions? Ask a IT Expert for Support ASAP

Ask a Computer Expert,
Get an Answer ASAP!

Microsoft Office
This answer was rated:

I am writing a macro which includes accessing data in stored

documents. To access an individual...
I am writing a macro which includes accessing data in stored documents. To access an individual cell, I use the contents of cell AM3 as the modifier:

myrow = Range("AM3")
Range = ("A" & myrow).Select

I now have to access a range of data, modified by cell AM3 and tried the following:
myrow = Range("AM3")
Range = ("A" & myrow : "C" & myrow + 8)

What is the correct coding ?
Show More
Show Less
Ask Your Own Microsoft Office Question
Answered in 2 minutes by:
9/16/2013
Richard
Richard, Software Specialist
Category: Microsoft Office
Satisfied Customers: 39,544
Experience: Over 15 year experience resolving Microsoft Office Issues
Verified
Thank you for your question, my name is Richard.

To clarify, say the range has 3 5 6 in three cells

you want the variable to have stored 356 so all three values?

Thank you

Ask Your Own Microsoft Office Question
Customer reply replied 4 years ago

Hi Richard,


I have a name and address stored in cells A115 to C123 , the next address in cells A165 to C173, the next in cells A215 to C223 etc. Cell AM3 contains the cell number of the start of the range, eg 115 or 165 or 215 etc


 


Does that help ?


 


My name is Robin

Nearly Robin
So basically you want to reference the cell that is specified in AM3

so AM3 has A165

And you want code that says get the value of the cell specified in AM3?

If you explain the overall achievement you want in your own words, that can help.

Thank you

Ask Your Own Microsoft Office Question
Customer reply replied 4 years ago

Not quite. Not a single cell, but a range of cells, which could be from A115 to C123, or A165 to C173, or A215 to C223 etc. The value in cell AM3 will be 115 or 165 or 215 etc , which will give the start cell number of the range.

Can I look at this spreadsheet?

It is easier

- Click the following link: http://www.wikisend.com
- Upload the file to that website
- Once it is uploaded, the resulting page will display a "File ID" number.
- Please, give me that "File ID Number"

Thank you
Ask Your Own Microsoft Office Question
Customer reply replied 4 years ago

The spreadsheet is over 10mg and is difficult to email. This question is intended for a Visual basic expert.

I am a Visual Basic Expert, and I dont want you to email the file.

I want you to upload it please.

- Click the following link: http://www.wikisend.com
- Upload the file to that website
- Once it is uploaded, the resulting page will display a "File ID" number.
- Please, give me that "File ID Number"

Ask Your Own Microsoft Office Question
Customer reply replied 4 years ago


Sorry, but I cannot spend any more time on this. I have an urgent appointment and will have to find someone else who can give me a quick answer.


Thanks for trying to help me

sure
Ask Your Own Microsoft Office Question
I'll take an educated guess, though I am still not clear on your code.

But you can use

Dim SrcRange As Range

Set SrcRange = Sheets("Src").Range("A" & Range("AM3") & " : " & Range("AM3")+8)

This puts the range, based on the value of AM3 into a Range variable.
Ask Your Own Microsoft Office Question
Customer reply replied 4 years ago

Thanks Richard.


Just so there is no confusion, the following would be my coding if I were copying a fixed cell to a destination cell.


 


Sheets("DATA").Select


Range("A115").Select


Selection.Copy


Sheets("CUST").Select


Range("A1").Select


ActiveSheet.Paste


 


If the source cell could be one of 100 different cells, the cell AM3 will be set to the value of the appropriate one to action. Thus the code would now be :


 


Sheets("DATA").Select


myrow = Range("AM3")


Range("A" & myrow).Select


Selection.Copy


Sheets("CUST").Select


Range("A1").Select


ActiveSheet.Paste


 


So, whatever value is assigned to cell AM3, that cell in column A will be selected.


 


Now I wish to go one step further. Instead of selecting a single cell, I have to select a range of cells.


 


If their location was fixed, the code would be


 


Sheets("DATA").Select


Range("A115..C120).Select


Selection.Copy


Sheets("CUST").Select


Range(A1").Select


ActiveSheet.Paste


 


However, because the source range may be one of 100, I was hoping that there was some code to use the contents of cell AM3 to specify the specific range that I required.


 


If that all makes sense could you let me have the code.


 


Many thanks in anticipation


 

ok, you would use the code as this

myRow = Range("AM3")

Sheets("DATA").Select

Range("A" & myRow & " : C120").Select

Selection.Copy

Sheets("CUST").Select

Range(A1").Select

ActiveSheet.Paste


This is the code you would use for this which would select the range based on the value of AM3

Let me know if you have any difficulties please

Thank you
Ask Your Own Microsoft Office Question
Customer reply replied 4 years ago

Great. Many thanks Richard.


One more thing. Could the source sheet, DATA, be variable as well?


 


In other words, is there code to access the sheet using a variable, such as AM3 is used for the cell, to set the Sheet which must be accessed ?

Yes, no problem at all

In my example I say that AM2 has the sheet name. You just use any cell you wish.

The code is as follows.

myRow = Range("AM3")
mySheet = Range("AM2")

Sheets(mySheet).Select

Range("A" & myRow & " : C120").Select

Selection.Copy

Sheets("CUST").Select

Range(A1").Select

ActiveSheet.Paste

If you have any problems, or would like any additional information or assistance, please do not hesitate to let me know.

And if you can take a second to rate my service, that would be greatly appreciated.

Thank you
Richard
Richard, Software Specialist
Category: Microsoft Office
Satisfied Customers: 39,544
Experience: Over 15 year experience resolving Microsoft Office Issues
Verified
Richard and 87 other Microsoft Office Specialists are ready to help you
Ask your own question now
Customer reply replied 4 years ago

Thanks so much Richard. I have done the rating.


One final point. In your answer you use cell C120. I would like to use C and myrow + 25.


In other words, If AM3 contains 115 the range extends from A115 to C140.


 


Could you rewrite just that one line of code ?


 


Many thanks, Robin

Of course

myRow = Range("AM3")
mySheet = Range("AM2")

Sheets(mySheet).Select

Range("A" & myRow & " : C" & myRow + 25).Select

Selection.Copy

Sheets("CUST").Select

Range("A1").Select

ActiveSheet.Paste

Let me know if you have any difficulties please

Thank you
Ask Your Own Microsoft Office Question
Customer reply replied 4 years ago

Sorry Richard, but I am getting an Error 1004 , Method 'Range' of object'_Global' failed

This means the value you are using is not valid in your code.

On which line do you get the error?

this is why I wanted the file as there are many variables that can cause this.

Ask Your Own Microsoft Office Question
Customer reply replied 4 years ago

Sorry. It is line number 4. Maybe I have got a space where I shouldn't have one.


 


My exact code is as follows :


 


Sheets("DATA").Select


mySheet - Range("AM3")


Sheets(mySheet).Select


myRow = Range("AC9")


Range("A" & myRow & " : H" & myRow + 25).Select


Selection.Copy


 


 

Yes, but it also invovles data on the sheet.

This is why I initially wanted to see the sheet

- Click the following link: http://www.wikisend.com
- Upload the file to that website
- Once it is uploaded, the resulting page will display a "File ID" number.
- Please, give me that "File ID Number"
Ask Your Own Microsoft Office Question
Customer reply replied 4 years ago

File ID No is 440880. The file should open at the point where you can activate the macro using Control and f.

This is the problem

myrow = Range("AC9")
Range("A" & myrow & " : H" & myrow + 50).Select

But Cell AC9 on Dat is empty

This is the reason for the error.

Ask Your Own Microsoft Office Question
Customer reply replied 4 years ago

O.K. but the line before myrow = Range("AC9") should specify Sheet 1 and cell AC9 of sheet 1 contains 114.

There is no Sheet 1 in your book

Ask Your Own Microsoft Office Question
Customer reply replied 4 years ago

When you open the uploaded version, you are in sheet 1. It is called 1 not sheet 1.

ok, the problem is you dont define the variable.

You need to do this with all variables

At the top, before the code starts, (under the Sub name)

Put

Dim mySheet As String

It needs to be a String Data Type

Let me know if you have any difficulties please
Ask Your Own Microsoft Office Question
Customer reply replied 4 years ago

Great !! It now works fine.


 


Many thanks for all the time that you have spent Richard

Anytime
Ask Your Own Microsoft Office Question
Ask Richard Your Own Question
Richard
Richard
Richard, Software Specialist
Category: Microsoft Office
Satisfied Customers: 39,544
39,544 Satisfied Customers
Experience: Over 15 year experience resolving Microsoft Office Issues

Richard is online now

A new question is answered every 9 seconds

How JustAnswer works:

  • Ask an ExpertExperts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional AnswerVia email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction GuaranteeRate the answer you receive.

JustAnswer in the News:

Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.

What Customers are Saying:

My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed.

One Happy CustomerNew York

Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help.

Mary C.Freshfield, Liverpool, UK

This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!!

AlexLos Angeles, CA

Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult.

GPHesperia, CA

I couldn't be more satisfied! This is the site I will always come to when I need a second opinion.

JustinKernersville, NC

Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around.

EstherWoodstock, NY

Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know.

RobinElkton, Maryland

< Previous | Next >

Meet the Experts:

jstinehelfer

jstinehelfer

Information Systems Manager

36 satisfied customers

A+ Comptia Certified computer repair

JasonJames122

JasonJames122

Computer Enthusiast

0 satisfied customers

I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business

Jess M.

Jess M.

Computer Support Specialist

684 satisfied customers

Computer Software Specialist for more than 10 years

The-PC-Guy

The-PC-Guy

Computer Manager

579 satisfied customers

20 years experience providing remote computer support

James K.

James K.

Consultant

260 satisfied customers

Technical Director of IT Company

IT Miro

IT Miro

Computer Scientist

156 satisfied customers

Bachelor's Degree in Information Technology, Microsoft Certified Professional

Chris L.

Chris L.

Support Specialist

143 satisfied customers

Certified Expert with over 10 years experience.

< Previous | Next >

Related Microsoft Office Questions
I am trying to sign in to my Microsoft account...
I am trying to sign in to my Microsoft account...***@******.*** … read more
UranIT
UranIT
IT Consultant, Network and System Admin
Associate Degree
465 satisfied customers
I mistakenling uninstall microsoft office. Can I recover it?
I mistakenling uninstall microsoft office. Can I recover it? … read more
Viet - Computer Tech
Viet - Computer Tech
Computer Technican
Bachelor\u0027s Degree
952 satisfied customers
I am coinstantly getting calls that say they are from
I am coinstantly getting calls that say they are from Microsoft....I have others also trying to call about my computer. How do I know if it is really a call from Microsoft. They are always saying my c… read more
Jess M.
Jess M.
Computer Support Specialist
Bachelor's Degree
684 satisfied customers
I have bought a new Windows 10 and have used my Office 365
I have bought a new Windows 10 and have used my Office 365 sub to install Office suite. I now get a message for me to fix my account in order to continue using the software. What do I need to do? … read more
Cody
Cody
Computer Science
1,466 satisfied customers
Uninstalled office 2011 for mac and then reinstalled from
uninstalled office 2011 for mac and then reinstalled from disk and outlook email not showing up … read more
Damian Costa
Damian Costa
Owner
Computer Network System Engineer
40 satisfied customers
I bought a microsoft office package on 18th Nov. for $99,
I bought a microsoft office package on 18th Nov. for $99, and it hasn't been re cognised on my computer. How doI activate it? … read more
Michelle
Michelle
Manager operations
Bachelor\u0027s Degree
1,273 satisfied customers
I CAN'T OPEN MICROSOFT WORD FILES ON MY DESKTOP. I GET AN
I GET AN ERROR MESSAGE. IT SAYS THE TITLE IS INCORRECT. THIS HAS NEVER HAPPENED BEFORE. I HAVEN'T KNOWINGLY REARRANGED FILE FOLDERS RECENTLY … read more
Cris
Cris
Desktop Support
Associate Degree
58 satisfied customers
I wish to go back to our previous email
I wish to go back to our previous email [*****@******.*** … read more
Jess M.
Jess M.
Computer Support Specialist
Bachelor's Degree
684 satisfied customers
How to install previously purchased microsoft office online
how to install previously purchased microsoft office online to a new computer … read more
Viet - Computer Tech
Viet - Computer Tech
Computer Technican
Bachelor\u0027s Degree
952 satisfied customers
I just started a requested update of MS Office for my
I just started a requested update of MS Office for my MacBook pro (2015). … read more
Jon
Jon
Bachelors Degree
390 satisfied customers
I Need help making changes to an existing spreadsheet.
I Need help making changes to an existing spreadsheet … read more
The-PC-Guy
The-PC-Guy
Computer Manager
Bachelor's Degree
579 satisfied customers
I just installed Microsoft office and they are in the dock
Hello, I just installed Microsoft office and they are in the dock but none of them will open! … read more
Josh
Josh
It Support Specialist
Associate Degree
5,127 satisfied customers
How do i change my outgoing message in my teleohone. Acer
how do i change my outgoing message in my teleohone … read more
Bhavik Joshi
Bhavik Joshi
Computer Science and Engineering
586 satisfied customers
I CAN'T OPEN MICROSOFT WORD FILES ON MY DESKTOP. I GET AN
I GET AN ERROR MESSAGE. IT SAYS THE TITLE IS INCORRECT. THIS HAS NEVER HAPPENED BEFORE. I HAVEN'T KNOWINGLY REARRANGED FILE FOLDERS RECENTLY … read more
Bryan
Bryan
IT Consultant
8,746 satisfied customers
I am unable to open a excel document it is saying it may
i am unable to open a excel document it is saying it may have been deleted, remnames or moved … read more
bbao
bbao
Freelance IT Consultant
74 satisfied customers
I have various solid lines going across the page that I
I have various solid lines going across the page that I can't delete...how can I get rid of them? … read more
Jess M.
Jess M.
Computer Support Specialist
Bachelor's Degree
684 satisfied customers
I bought Microsoft Word for my at home computer, I would now
I bought Microsoft Word for my at home computer, I would now like to install it on my MacLaptop. What do I need to do? … read more
Kamil Anwar
Kamil Anwar
IT Consultant / Systems Engineer
Bachelor\u0027s Degree
426 satisfied customers
I have addresses, dates, and duration on Sheet 1. There are
I have addresses, dates, and duration on Sheet 1. There are duplicate addresses but always unique dates. For example: Column A Column B Column C Address Date Duration*****10/1/17 1 year Example St 1/1… read more
The-PC-Guy
The-PC-Guy
Computer Manager
Bachelor's Degree
579 satisfied customers
Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Show MoreShow Less

Ask Your Question

x