• Hi All

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

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Cannot see why code doesn't work - please help

cricketnz

New Member
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:
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.
 
Hi CricketKiwi

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

Code:
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
 
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:
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)
 
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:
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)

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
 
Hi CricketKiwi

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

Code:
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

Hi Smallman,

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

Cricketnz
 
Hi Cricketnz,

My bad, didn't test it - this should work though:
Code:
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
 
Hi Cricketnz,

My bad, didn't test it - this should work though:
Code:
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

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

Cricketnz
 
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
 
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
 
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
 
Excellent - then let's agree to agree! :)

Your solution is certainly more elegant, but I'd suggest one amendment:
Code:
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
 
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
 
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
 
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

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?
 
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
 
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
 
Back
Top