LinkBack URL
About LinkBacks
Hello to all.
This macro is for finding or replacing codes in sheets.
Works in all cells.
Is it possible to change it because it works only for column O?
Thank you
john
Please Login or Register to view this content.
Hello to all.
This macro is for finding or replacing codes in sheets.
Works in all cells.
Is it possible to change it because it works only for column O?
Thank you
john
Please Login or Register to view this content.
Hi there,
I haven't looked at your code in detail, but you really should NOT begin a routine with an open-ended:
Please Login or Register to view this content.
Doing this means that you have greatly reduced the control you have on how your code behaves if an error is encountered.
Regards,
Greg M
I don't see how the code will only work for column O
is it better to remove?
on error resume next
Hi again,
Yes indeed, it is DEFINITELY better to remove it in this case. It's quite possible that after doing this your code will show an error message when you run it, but at least you'll have some indication of what the error is, and where it occurred. At present your code might be generating several errors, but you have no idea whether or not it is.
In many circumstances the "On Error Resume Next" statement can be very useful, but it should always be paired with an "On Error GoTo 0" statement to ensure that error handling is re-enabled as soon as possible.
The following routine shows how disabling error handling and then re-enabling it (almost immediately afterwards) can be used to detect whether or not a specific worksheet exists:
Please Login or Register to view this content.
Hope this helps.
Regards,
Greg M
Thanks greg, I take off on error resume next.
Hello to all.
Has anyone figured out how to fix the problem
Refer to post #3
Hi davesexcel.
But an alternative code is not possible?
Nowhere in the code does it only work for Column O, I tested the code and it works for the entire sheet..
If it only works for column O on your sheet, you will have to supply the sample WB.
The macro post #1 works on all columns
I ask if it is possible to change by column O
**English to Italian via DuckDuckGo search enginePlease Login or Register to view this content.
Last edited by protonLeah; 09-09-2021 at 05:56 PM.
Ben Van Johnson
Hi protonleah work well, thank you.
I added lines to see how many codes were found, but it doesn't work well
Please Login or Register to view this content.
"...Doesn't work well..." Isn't enough information. In your latest code, you set the desired search column to "E". But, in the countif line, you are searching the entire sheet: CountIf(Cells, TrovatoSu). Do you want to count the whole sheet or just the search column ("E")?
Also, you are testing for numeric value from the inputbox but I don't know if you are searching for numeric or text values...
IsNumeric(TrovatoSu) will fail if TrovatoSu is a text value
Hi protonleath,
must search for both numbers and text and search column ("E")
Last edited by john_cash; 09-12-2021 at 12:56 AM.
Hi to all.
I corrected it but it doesn't work the message correctly
result_1 = Application.WorksheetFunction.CountIf(Columns(Col), TrovatoSu)
example:Please Login or Register to view this content.
clik in button all column cells
FINDS COLUMNS = I
FIND = CIAO
result = 2
REPLACE = CIAO2
the resut it must be 7
Last edited by john_cash; 09-14-2021 at 07:04 AM.
In column I, you have:You search for the string: CIAO and the first instance is at row 8, (CIAO1O).
CIAO1O
CIAO1O
CIAO1O99
CIAO1O44
CIAO1O55
CIAO1O15
CIAO1O23
Your COUNTIF function is then counting instances of CIAO1O not CIAO --> 2 vs. 7
So, try a wildcard count:
Please Login or Register to view this content.
Last edited by protonLeah; 09-14-2021 at 11:15 PM.
Hi I added the change but it doesn't change, it always results in quantity 1 .
Example:
button "tutte le celle colonna" = N
button "trova" = ABCD
result = 1
exact result = 11
Maybe you should count the original search value,e.g. ABCD:
result_1 = Application.WorksheetFunction.CountIf(Columns(Col), VecchioValore & "*")
You are searching for both numeric and text values. The cells with numeric values, F8=148, are formatted as general. But the value from the inputbox is a string so when passed to the COUNTIF function will return 0. So I added code to test if the source data is numeric and convert it to string for the count..
Please Login or Register to view this content.
Thank you protonleath, the modification works fine.
edit thread
Last edited by john_cash; 09-16-2021 at 01:43 PM.
I tried to modify it to find all numbers like macro post #20
It doesn't work well.
button "tutte le celle"
button "trova" = 51
result = 16
exact result = 20
Please Login or Register to view this content.
Last edited by john_cash; 09-16-2021 at 01:45 PM.
Hi
result_1 = Application.WorksheetFunction.CountIf(Cells, VecchioValore) '<<< work
Else
result_1 = Application.WorksheetFunction.CountIf(Cells, "*" & VecchioValore & "*") 'not work
51 'found
51 'found
51 'found
51 'found
51 'found
51 'found
51 'found
51 'found
51 'found
a-51 'not found
51 'found
51 'found
51-abcd 'not found
51 'found
123456-51 'not found
51 'found
51 'found
51 'found
51 'found
What is this for?Please Login or Register to view this content.
You are changing the variable value. The fist one it finds is 51, so it will count 51 only.
Last edited by davesexcel; 09-18-2021 at 10:23 AM.
Hi,Please Login or Register to view this content.
if in inputbox "trova" = value not present in the sheet
the macro goes into debug
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
,
Bookmarks