1. Hi All

    Please note that there is Zero Tolerance to Spam at Chandoo.org

    Post Spam and you Will Be Deleted as a User

    Hui...

Cannot see why code doesn't work - please help

Discussion in 'VBA Macros' started by cricketnz, Sep 10, 2013.

  1. cricketnz

    cricketnz New Member

    Messages:
    12
    Hi,

    Can anybody tell me why below code does NOT put the formula or its result in the selected cells? I can not see what I'm missing here, but I'm certainly are missing something.
    What I want to do is based on the presence of a search string in a selected range of cells (all in the same column) add the word "STOCK") in a column offset (0,4) to the right on the same rows, and if not to leave the cell blank

    Code (vb):
    Sub SelUserRange()
    Dim UserRange As Range
    Dim TopCell As Range
    Dim Bb, StrSrch, StrRepl As String
    StrSrch = "NON INVENTORY:"
    StrRepl = "STOCK"
    Bb = ""
    On Error GoTo Canceled
    Set UserRange = Application.InputBox(Prompt:="Please Select Range of Items that need to be converted", Title:="Range Select", Type:=8)
    UserRange.Offset(0, 4).Formula = "=IF(ISNUMBER(FIND(" & StrSrch & "," & UserRange(1, 1).Address(False, False).Value & ")}," & StrRepl & "," & Bb & ")"
    Canceled:
    End Sub
    If I put =IF(ISNUMBER(FIND("NON INVENTORY",B441)),"STOCK"," ") in any of the offset cells it works fine
    The code also works with UserRange.Offset(0,4).Formula = "=" & UserRange(1, 1).Address(False, False

    Your feedback will be greatly appreciated.
  2. Smallman

    Smallman Active Member

    Messages:
    883
    Hi CricketKiwi

    Go with the following instead. If you can't get it rolling I will upload a file which works nicely.

    Code (vb):
    Sub testo()
    Range("A1", Range("A65536").End(xlUp)).AutoFilter 1, "Non" & "*"
    Range("A2", Range("A65536").End(xlUp)).SpecialCells(12).Offset(, 4).Value = "Stock"
    Range("A1").AutoFilter
    End Sub
     
    Take care

    Smallman
  3. roe3p

    roe3p New Member

    Messages:
    5
    Hi,

    The problem is that the output formula is missing all its quotes - so your final formula is reading:

    =IF(ISNUMBER(FIND(NON INVENTORY,B441)),STOCK, )

    rather than:

    =IF(ISNUMBER(FIND("NON INVENTORY",B441)),"STOCK"," ")

    You just need to add double-double-quotes into your code as follows:

    Code (vb):
    UserRange.Offset(0, 4).Formula = "=IF(ISNUMBER(FIND(""" & StrSrch & """,""" & UserRange(1, 1).Address(False, False).Value & """)},""" & StrRepl & ""","""")"
    (I've also removed the Bb variable as it's just read as an empty string rather than a double quote)
  4. cricketnz

    cricketnz New Member

    Messages:
    12
    Hi Roe3p

    Your comments made a lot of sense, so I was quick to try it out. Unfortunately, it is still not doing anything.
    If you'd like I'm happy to upload a file
  5. cricketnz

    cricketnz New Member

    Messages:
    12
    Hi Smallman,

    Many thanks for your reply, however I need more control hence the idea of selecting small ranges of the spreadsheet.

    Cricketnz
  6. roe3p

    roe3p New Member

    Messages:
    5
    Hi Cricketnz,

    My bad, didn't test it - this should work though:
    Code (vb):
    UserRange.Offset(0, 4).Formula = "=IF(ISNUMBER(FIND(""" & StrSrch & """," & UserRange.Cells(1, 1).Address(False, False) & ")),""" & StrRepl & ""","""")"
     
    I changed a curly bracket to a normal one, removed the .Value from the end of .Address and removed the double quotes from around it as well. Seems to work fine now.

    Hope this helps!

    roe3p
  7. cricketnz

    cricketnz New Member

    Messages:
    12
    Marvelous! Yes, it did! How one wrong bracket can make a difference ...Much appreciated indeed!

    Cricketnz
  8. Smallman

    Smallman Active Member

    Messages:
    883
    Hi CricketKiwi

    Control in VB is gained by knowing your starting point (or trapping it) and designing code around that point. Once you trap the start you can trap the last instance in the column you are working with. Selection in the world of vba is not something you should get into just because the Recorder selects objects.

    There is always a better way.

    Smallman
  9. roe3p

    roe3p New Member

    Messages:
    5
    Hi Smallman,

    I agree in general, but it can often be handy to have subs that only work on a targeted range (especially when working with sheets manually populated by someone else!)

    There may well be a better way to achieve what's needed in this case, but what Cricketnz has so far seems like a neat enough way to avoid blindly using 'Selection.', so I've answered the OP to help understand the issues that arise when populating cell formulas from vba.

    Cheers,
    roe3p
  10. Smallman

    Smallman Active Member

    Messages:
    883
    Roe3p

    I was neither addressing you, your answer or having a go. Here is how I look at every problem bar none. I will not look at the Op's code for anything but guidance for what the process is trying to achieve. Then I will completely re write the whole thing. I believe I can achieve an efficient outcome and convey a bit of learning in the process. And that is the most important thing to take away. If you do it the way that has been suggested in most cases you are marching down the path of a sub optimal solution. That is a journey I never travel.

    I still think this solution well thought through can completely avoid selection in any form. There is no file so no way to tell. CricketKiwi has two completely different solutions - in this case he will most likely not choose my solution - but hundreds of people will eventually stop by this thread and my method will be of use to some I am sure.

    Smallman
  11. roe3p

    roe3p New Member

    Messages:
    5
    Excellent - then let's agree to agree! :)

    Your solution is certainly more elegant, but I'd suggest one amendment:
    Code (VB):
    Range("A1", Cells(Rows.count, 1).End(xlUp))
    to define the range, rather than "A65536" - just in case the user is using Excel 2007+ and has more then 65536 rows of data. (without intending to pick nits ;))

    Cheers,
    roe3p


    p.s. - I'm new to this forum and I can't seem to select VB as my code type so it always comes out as Code (text). Can you tell me how I can output Code (vb) like you guys? Do I select PHP rather than General Code? Thanks!

    Edit: by Hui
    Marc L likes this.
  12. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    8,618
    Hi ,

    When you click on the Code button , and insert your code , the software inserts the two tags "[" CODE "]" and "[" /CODE "]" , with your code in between them.

    Modify the initial tag to "[" CODE=VB "]". That is all there is to it.

    The only problem with this seems to be that the indenting changes , since the font is not a fixed font but a proportional font. Of course , if you change to a fixed font like Courier New , then it inserts a tag on every line , which makes it very difficult to read.

    Note that I have put the square brackets within quotes , since otherwise the software interprets the code tags literally.

    Narayan
  13. roe3p

    roe3p New Member

    Messages:
    5
    Code (VB):
    Dim test as String
    That's great, thanks for your help!

    Cheers,
    roe3p
  14. Smallman

    Smallman Active Member

    Messages:
    883
    Yeah I get pulled up on the 65536 thing a bit. My view is that 99% of the time I will be ok and when writing code for others and myself (few and far between) it is 100% as I don't think while XL has 1m plus rows you should use XL for a data repository that large. I use Access when the data set gets any bigger than 30k rows.

    Take care

    Smallman
  15. cricketnz

    cricketnz New Member

    Messages:
    12
    Hi Smallman

    I must admit your code is actually very smart, and I am warming up to it. excel-data.jpg
    May I ask your advice for an other challenge?
    please note attached data snippet

    How would I go about using the auto filter solution to put in the offset the value of column F when the condition of the autofilter is not satisfied?
  16. Smallman

    Smallman Active Member

    Messages:
    883
    Hi CricketKiwi

    I am not sure I understand your question. What column do you want to filter on and what is the criteria? What value do you want to put in a column when this condition is not met?

    I am about to fly abroad and this is probably a thread I will not be able to follow through on. I am sorry about that. There are however many smart members who will be able to further assist with your problem.

    Take care

    Smallman
  17. cricketnz

    cricketnz New Member

    Messages:
    12
    Hi

    The condition is as before and the column being filtered for the condition is column B. If the condition is met we put the word "Stock" in column G.
    If the condition is not met, I would like to copy the value of column F (as offset) in to column G
    I was wondering if we could use another auto filter for the case the first condition is not met by stating a condition that is the reverse, I.e. condition2 <> condition1

    Cheers,
    Cricketnz

Share This Page