Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Help with macros


  • Please log in to reply

#1
Katralic

Katralic

    New Member

  • Member
  • Pip
  • 3 posts
I have excel 2007
I'm just learning macros. Here's the setup.
 
sheet 1 is for getting the miles from city a to city b
i.e. a1 I input city a. a2 I input City b the on b2 it will tell me what the mileage is.
 
Sheet 2 has my mileage chart where all the mileage info is stored from city to city
It's pretty much set up like every other mileage chart you see on a map with the city name across the top row and the same name on the left column
then the mileage is set in the grid
 

every once in a while I will input a city name into sheet 1 and I haven't yet input that number into sheet 2 yet so 0 is displayed
I have added  3 cells in sheet 1 to automatically enter the correct mileage into the mileage chart
 
the 1st cell I input city a
the 2nd cell city b and the 3rd cell the mileage
 
 
the next thing I have are 3 cells in sheet 2
the 1st cell (lets call it A1) tells me the r#c# of the intersection of the 2 cities
the 2nd one (A2) tells me the opposite r#c# since each mileage is listed twice depending on whether its from city 1 to city 2 or city 2 to city 1
the 3rd (A3) is the mileage that I input in sheet 1
 
now what ive been doing is just copying the R#c# from A1 and pasting it into the name box and hitting enter. It then brings me to that location
and then I type in the mileage
then copy and paste the 2nd address located in A2 into the name box and entering the miles again.
 

what I want is a macro so all I have to do after entering the 2 city names and mileage into sheet 1
is run the macro and have it automatically go to the addresses specified in sheet 2 and input the mileage in for me
every time I try this the macro does not record me pasting the R#C# from A1 and A2 into the name box and hitting enter.
It just inputs the mileage into the same boxes every time. Since the address is going to be different every time this just wont work.
 
So how to make a macro that goes to a certain address located in a separate cells (A1and A2) and inputs information into those cells thats located in a separate cell (A3)


Edited by Katralic, 21 November 2015 - 11:21 AM.

  • 0

Advertisements


#2
RKinner

RKinner

    Malware Expert

  • Expert
  • 17,336 posts
  • MVP

I'm trying to understand what you are trying to do.  Is the city already in your sheet 2 chart or does it sometimes need to be added?  Or we just filling in gaps in the mileage part of the table?  Could you perhaps send me an attached copy of the spreadsheet (or a dummy copy if it's sensitive)?  (Use a PM if you like.)


  • 0

#3
Katralic

Katralic

    New Member

  • Topic Starter
  • Member
  • Pip
  • 3 posts

I've been trying to upload the file but it wont let me. I disabled all macros and saved it as a non macro file but still tells me I'm not allowed to upload this type of file


  • 0

#4
Katralic

Katralic

    New Member

  • Topic Starter
  • Member
  • Pip
  • 3 posts

I was told the solution on the VBA Express Forum. I asked the question on the 19th at 10 pm woke up on the 20th with 2 correct answers. This is the one I used

 

                  

Sub Insert_mileage()
 range(Application.ConvertFormula(range("A1").Value, xlR1C1, xlA1)) = range("A3").Value
 range(Application.ConvertFormula(range("A2").Value, xlR1C1, xlA1)) = range("A3").Value
 End Sub​


  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP