1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Simple Excel VBA question

Discussion in 'Business Applications' started by sarumitai, Apr 10, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. sarumitai

    sarumitai Thread Starter

    Joined:
    Apr 9, 2007
    Messages:
    6
    I have done some elaborate things in Excel VBA before, I even automated my week's work into one huge Macro, and yet today I have turned temporarily stupid. Granted I am usually piecing together code snippets since I'm not a true programmer, but this seems so simple.

    I'm trying to make a worksheet that conjugates Japanese verbs. I can do everything except this one simple little thing. When users click on a word on Worksheet C, there is a button they press that copies the word and pastes it into cell A1 of Worksheet A. For some reason I can't get it to post into A1, it throws an error and I don't know why. I have run through the process and recorded the macro several times and it records it fine, but when I run it, I get this error.

    Selection.Copy

    Sheets("Display").Select
    Range("B1").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False

    The troublemaker is Range("B1").Select. Everything else works fine. I simply want the copied data to be pasted into cell B1, but it won't.
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,678
  3. sarumitai

    sarumitai Thread Starter

    Joined:
    Apr 9, 2007
    Messages:
    6
    I'm reading through it now and it seems to be what I need. I've never even thought of questioning the use of select so this is new to me.

    Thanks.
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Thanks for the vote Tony. LOL!

    @sarumitai: A misconception comes when people migrate from standard solutions to VBA. The idea is that, "I can do it in Excel, I should be able to do it the same way with VBA." The problem is that the Object Model (what we use for our Object Oriented Programming in Excel) does not necessarily imitate native Excel functionality.

    For example, for moving data, you can just transfer the data straight across...

    Code:
    Sheets("Display").range("B1").value = activecell.value
    Now, there really isn't much need to Select or Activate things, unless the actions you are taking actions straight from a selected cell(s), which it seems you are. This makes me think about the methodology you are taking for your solution(s) here. Not saying it is bad, but there may be an overall better way.

    As far as copying, there is a better way than to use the Copy method which is to use DataObjects. This is fast and uses the Windows Clipboard, as opposed to the Office Clipboard (there is a difference between the two, confusing, I know). For a straight value shift, this is a bit much.

    Maybe you could post more about your project and what you are wanting as the final outcome. Leaving the methodologies open will generally get you more than one way to get your solution(s).

    HTH
     
  5. sarumitai

    sarumitai Thread Starter

    Joined:
    Apr 9, 2007
    Messages:
    6
    Basically I just want to copy one cell (that is the activecell) to another specific cell on a a different sheet.

    The usage now is for my grade sheet. I am writing a macro that I will use when I want to generate a report for one student. If his/her name is selected, I'd like to press some key combo that copies that name to a separate sheet to a specific cell. From there I would VLOOKUP various other info and populate other cells based on the name.

    There is almost always a better way to do things on my spreadsheets. I've never been formally trained and just piece things together. You don't want to see how I generate random numbers...

    I guess a simple example would be:

    There are 5 names, 1 each in cells A1 to A5 on sheet NAMES. When any cell is selected, let's say A3, I would initiate a macro that copies that name to sheet REPORT into cell A1.

    Yea, it's easy on the same sheet and I have been thinking about just moving everything to the same sheet, but I really just want to conquer this beast.

    Thanks for all the help.
     
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hmm, why Select it though? There must be some other catalyst which you can use here. What about using a UserForm for this? Or even a listbox or radio buttons? Relying on the selected cell will prove quite a pain to deal with I'm thinking.
     
  7. sarumitai

    sarumitai Thread Starter

    Joined:
    Apr 9, 2007
    Messages:
    6
    I thought it would be simple when I started. Users could simply select a box with the mouse and then click a button and the macro would do the rest.

    A drop down listbox or combo box would be fine as long as I can populate it dynamically from a range on a worksheet. I have been researching that and made some progress with it, but any advice would be great.
     
  8. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
  9. sarumitai

    sarumitai Thread Starter

    Joined:
    Apr 9, 2007
    Messages:
    6
    This is more or less doing what I want and thanks for the link. That's a great site. I've given up on my original plan and now have everything on one sheet with the processing parts off screen. It seems to work though I can't get the dynamic range to populate the listbox. The listbox shows, but doesn't list. That's not a huge thing since I won't be adding things to the range daily, but I do love the "build once" idea.

    Part of my problem could be that I am on a Japanese computer with a Japanese OS and Japanese MS Office in Japan. I spend part of my time trying to track down which menu is what.
     
  10. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Ah yes. You might want to check out Colo's site then (http://puremis.net/excel/indexJ.shtml). That is his Japanese site with some tips and a board as well. He has it in English as well.

    As for your problem, it depends on what kind of ListBox you've created. There are two kinds: a Forms ListBox and a Controls Toolbox ListBox. They're populated in separate ways and have separate attributes and properties. Please tell us what you've done thus far. Without that, we won't know what you have or, more importantly, how to fix it. :)
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/560482

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice