+ Reply to Thread
Results 1 to 7 of 7

Attempting to Create an If Function

  1. #1
    Registered User
    Join Date
    06-12-2013
    Location
    Poughkeepsie, NY
    MS-Off Ver
    Excel 2003
    Posts
    36

    Attempting to Create an If Function

    Hello again, Excel Forum,

    I am attempting to write an if function in Excel 2003 that determines whether or not a certain cell is populated and if it is, to move that cell's contents to another cell. If the original cell is not populated, then I would like the second cell to populate with a formula that references other cells in that worksheet. What I have so far is:

    Please Login or Register  to view this content.
    This is a very basic version of what I have to do, but I would be able to figure out how to manipulate the references in order to match my data.

    The problem I'm seeing is that the range referencing that I'm doing is wrong, but I'm not 100% sure other ways to do it. Everything that is being referenced/pasted is all in the same sheet. Also, this if function has to be performed 36x14 times, so I don't know how to fit it into one macro because of size.

    Any help on any of these problems would be greatly appreciated. Thanks in advance for your help as always.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Attempting to Create an If Function

    First, all that Chr(34) is messing you up. I don't see why you are using that, unless you actually have quotes as part of your sheet name.

    Also, it is not necessary to do a literal copy and paste to copy a value. Use Copy & Paste only if you need to copy value, formula, formats, data validation, and everything else.

    Where does this code reside? If it is in the module for Sheet1, then it is unnecessary to qualify every range reference with

    Sheets("Sheet1").

    See if this helps:
    Please Login or Register  to view this content.
    It's hard to give you any further advice without getting the bigger picture of what you're trying to do, for example I have no idea what 36x14 times means here.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    06-12-2013
    Location
    Poughkeepsie, NY
    MS-Off Ver
    Excel 2003
    Posts
    36

    Re: Attempting to Create an If Function

    I apologize for not being more in depth, I was attempting to simplify it so I could use my own knowledge to try and continue learning this language and I realized that it wouldn't help if you didn't know exactly what I am trying to accomplish.

    I didn't realize that Chr(34) was used only for sheets with quotes (which I do have in this case). The code will reside in the personal workbook so that someone can open a new updated version of the workbook they receive, so I think I will need to reference each sheet with Sheets(Sheet1). In my case though, the sheet being referenced will be "P" (with the quotes) so I'm assuming that I would need the Chr(34) in order to reference them correctly.

    Apologies on the 36x14 that makes absolutely no sense looking back on it. What I mean by it is that I have to do this if statement check for each "platform". There are 14 different "platforms" that consist of 36 months that make up 36 different rows. Within each row there are 15 columns that need to be checked for nonzero values using the if statement. So as you can see, it will get very large very quickly.

    The code you provided helps a great deal by the way. Thanks so much for the response.

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Attempting to Create an If Function

    Quote Originally Posted by vbaNewbieMan View Post
    I didn't realize that Chr(34) was used only for sheets with quotes (which I do have in this case). The code will reside in the personal workbook so that someone can open a new updated version of the workbook they receive, so I think I will need to reference each sheet with Sheets(Sheet1). In my case though, the sheet being referenced will be "P" (with the quotes) so I'm assuming that I would need the Chr(34) in order to reference them correctly.
    I think you have some confusion about how quotes work when referring to sheet names. Let me see if I can explain this.

    By default when you open Excel, you will see a sheet named

    Sheet1

    That is what I assume you have. To refer to this sheet in your code, you can use a string containing the sheet name as an index in the collection called Worksheets (or Sheets). To make a string, simply enclose the sheet name in double quotes. This is done as in the code I provided:
    Please Login or Register  to view this content.
    The sheet name is enclosed in quotes. The string returned is

    Sheet1

    same as what the tab shows.

    The character Chr(34) is the double quote character ("). When you add that, you are adding quotes to the actual sheet name. It is certainly possible to create a sheet name with quotes, but I would be surprised if that's what you intend. You can edit a sheet name to see this on the tab:

    "Sheet1"

    If you do have a sheet name like this, then you would need to modify the code to look like your original code, or alternatively this:
    Please Login or Register  to view this content.
    In VBA, to show a double quote inside a quoted string you can repeat the quote twice in succession (or concatenate Chr(34) as you have done).

  5. #5
    Registered User
    Join Date
    06-12-2013
    Location
    Poughkeepsie, NY
    MS-Off Ver
    Excel 2003
    Posts
    36

    Re: Attempting to Create an If Function

    Awesome thanks for that clarification, that will help greatly in the future as well as for this current problem.

    Back to my original problem, would there be anyway to shorten the code for an entire row that would contain 15 columns worth of "checking"? So it'd be a range from C1:Q1 and if the row is blank (which would be the case; individual cells in a row would not be blank where others have value), it would input the correct equation for the correct cell (i.e. cell W1 = C1*Q1, cell X = D1*P1, ect)? This would save an immense amount of space and would likely help with keeping all of whats needed within one macro.

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Attempting to Create an If Function

    Based on your description this will process C1:Q1. I have not tested it but I have compiled it. I am confused that you are referring to C1:Q1 but your code refers to A1, B1, C1, D1. I took my best guess at what you are trying to do.

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    06-12-2013
    Location
    Poughkeepsie, NY
    MS-Off Ver
    Excel 2003
    Posts
    36

    Re: Attempting to Create an If Function

    Once again, I apologize for the confusion. I originally posted those cells as a super simplified version.

    Lets say I need to check to see if C1:Q1 have those conditions and want to paste that row to AC1:AQ1. Would I still use that "Offset" function or what type of function would I use? The original copy/paste?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Attempting to create macro that finds a value and then transposes that row to a column
    By exoscoriae in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-05-2014, 11:49 AM
  2. Attempting Macro to hide current tabs and create new ones with same info (Diff tab name)
    By theta25nupe in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-14-2014, 11:44 AM
  3. Attempting to create a tag cloud, or something similar.
    By Kallisti in forum Excel General
    Replies: 2
    Last Post: 06-22-2012, 03:52 AM
  4. Replies: 16
    Last Post: 06-25-2011, 05:14 PM
  5. Replies: 3
    Last Post: 11-17-2010, 10:03 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1