Hi All,
New to this forum because I can't find the information I require
I have a workbook with several sheets, I have a data sheet and a sheet that collects from the data sheet and give information using COUNTIF functions
The data part is added by a few users and I used to have it in Google sheets where it worked perfectly but because I use * function to search MS excel does not see numbers in the search and count.
So I have been looking at doing an automatic change using VB instead of having to do a find and replace every time data is added.
I have 4 items I wish to change in the same column H:H, If the loaded data is 22HG1 or 220G1 for instance I want it to be changed to 2 then this =COUNTIFS(January_Data!$C:$C,"08/01/2021",January_Data!$H:$H,"2") will work where before is was =COUNTIFS(January_Data!$C:$C,"08/01/2021",January_Data!$H:$H,"2*") and the first one would give a value of 500 and the actual would be 700
I have tried a few VBA scripts I found online but nothing seems to work.
Thanks in advance
D
New to this forum because I can't find the information I require
I have a workbook with several sheets, I have a data sheet and a sheet that collects from the data sheet and give information using COUNTIF functions
The data part is added by a few users and I used to have it in Google sheets where it worked perfectly but because I use * function to search MS excel does not see numbers in the search and count.
So I have been looking at doing an automatic change using VB instead of having to do a find and replace every time data is added.
I have 4 items I wish to change in the same column H:H, If the loaded data is 22HG1 or 220G1 for instance I want it to be changed to 2 then this =COUNTIFS(January_Data!$C:$C,"08/01/2021",January_Data!$H:$H,"2") will work where before is was =COUNTIFS(January_Data!$C:$C,"08/01/2021",January_Data!$H:$H,"2*") and the first one would give a value of 500 and the actual would be 700
I have tried a few VBA scripts I found online but nothing seems to work.
202009010012300081 | 9/1/2020 | 12:46:52 AM | 22G1 | ||||
202009010012300093 | 9/1/2020 | 12:49:42 AM | 22G0 | ||||
202009010012300094 | 9/1/2020 | 12:49:42 AM | 22G1 | ||||
202009010012300099 | 9/1/2020 | 12:59:10 AM | 22G1 | ||||
202009010012300100 | 9/1/2020 | 12:59:10 AM | 22G1 | ||||
202009010012300104 | 40820185326 | 9/1/2020 | 1:03:25 AM | 22G1 | |||
202009010012300108 | 9/1/2020 | 1:01:15 AM | 22G1 | ||||
202009010012300115 | 40820155968 | 9/1/2020 | 1:09:37 AM | 2210 | |||
202009010012300116 | 40820155968 | 9/1/2020 | 1:09:37 AM | 2210 | |||
202009010012300127 | 40820155968 | 9/1/2020 | 1:24:13 AM | 2210 | |||
202009010012300128 | 40820155968 | 9/1/2020 | 1:24:13 AM | 2210 | |||
202009010012300129 | 40820175648 | 9/1/2020 | 1:25:03 AM | 2210 | |||
202009010012300130 | 40820175648 | 9/1/2020 | 1:25:03 AM | 2210 | |||
202009010012300133 | 40820185326 | 9/1/2020 | 1:25:17 AM | 22G1 | |||
202009010012300134 | 40820185326 | 9/1/2020 | 1:25:17 AM | 22G1 | |||
202009010012300139 | 40820178751 | 9/1/2020 | 1:27:03 AM | 22G1 | |||
202009010012300140 | 40820178751 | 9/1/2020 | 1:27:03 AM | 22G1 | |||
202009010012300153 | 40820175435 | 9/1/2020 | 1:32:02 AM | 2210 | |||
202009010012300154 | 40820175435 | 9/1/2020 | 1:32:02 AM | 2210 | |||
202009010012300192 | 9/1/2020 | 2:39:36 AM | 22G1 | ||||
202009010012300202 | 9/1/2020 | 2:38:43 AM | 22G1 | ||||
202009010012300204 | M1900006288 | 9/1/2020 | 2:47:57 AM | 22G1 | |||
202009010012300205 | 40820141658 | 9/1/2020 | 2:48:11 AM | 2210 | |||
202009010012300206 | 40820141658 | 9/1/2020 | 2:48:11 AM | 2210 | |||
202009010012300305 | 40820184238 | 9/1/2020 | 5:57:24 AM | 22G1 | |||
202009010012300306 | 40820184238 | 9/1/2020 | 5:57:24 AM | 22G1 | |||
202009010012300311 | 40820160072 | 9/1/2020 | 5:59:45 AM | 22G1 | |||
202009010012300312 | 40820160072 | 9/1/2020 | 5:59:45 AM | 22G1 | |||
202009010012300325 | 40820185326 | 9/1/2020 | 6:14:26 AM | 22G1 | |||
202009010012300326 | 40820185326 | 9/1/2020 | 6:14:26 AM | 22G1 | |||
202009010012300328 | 40820183948 | 9/1/2020 | 6:07:35 AM | 45G1 | |||
202009010012300369 | 90820182029 | 9/1/2020 | 8:09:46 AM | 2210 | |||
202009010012300370 | 90820182029 | 9/1/2020 | 8:09:46 AM | 2210 | |||
202009010012300375 | 40820141658 | 9/1/2020 | 8:19:49 AM | 2210 | |||
202009010012300376 | 40820141658 | 9/1/2020 | 8:19:49 AM | 2210 | |||
202009010012300389 | 40820160072 | 9/1/2020 | 10:29:23 AM | 22G1 | |||
202009010012300390 | 40820160072 | 9/1/2020 | 10:29:23 AM | 22G1 | |||
202009010012300399 | 70820142419 | 9/1/2020 | 8:02:36 AM | 2210 | |||
202009010012300400 | 70820142419 | 9/1/2020 | 8:02:36 AM | 2210 | |||
202009010012300401 | 40820160072 | 9/1/2020 | 8:03:12 AM | 22G1 | |||
202009010012300402 | 40820160072 | 9/1/2020 | 8:03:12 AM | 22G1 | |||
202009010012300412 | 9/1/2020 | 7:47:02 AM | 2210 | ||||
202009010012300445 | 40820181064 | 9/1/2020 | 11:47:02 AM | 2210 | |||
202009010012300446 | 40820181064 | 9/1/2020 | 11:47:02 AM | 2210 | |||
202009010012300449 | 40820141658 | 9/1/2020 | 11:54:34 AM | 2210 | |||
202009010012300450 | 40820141658 | 9/1/2020 | 11:54:34 AM | 2210 | |||
202009010012300461 | 40720127406 | 9/1/2020 | 12:07:40 PM | 22G1 | |||
202009010012300462 | 40720127406 | 9/1/2020 | 12:07:40 PM | 22G1 | |||
202009010012300473 | 40820154597 | 9/1/2020 | 12:11:14 PM | 22G1 | |||
202009010012300474 | 40820154597 | 9/1/2020 | 12:11:14 PM | 22G1 | |||
202009010012300510 | 9/1/2020 | 12:53:04 PM | 22G1 | ||||
202009010012300548 | 40820142700 | 9/1/2020 | 1:17:41 PM | 45G0 | |||
202009010012300556 | 70820135967 | 9/1/2020 | 1:19:47 PM | 22G1 | |||
202009010012300566 | 40820155439 | 9/1/2020 | 1:22:50 PM | 45G1 | |||
202009010012300573 | 40820164429 | 9/1/2020 | 1:18:44 PM | 22G1 | |||
202009010012300578 | 70820154602 | 9/1/2020 | 1:25:01 PM | 22G1 | |||
202009010012300584 | 70820154602 | 9/1/2020 | 1:25:01 PM | 22G1 | |||
202009010012300600 | 40820155530 | 9/1/2020 | 1:29:39 PM | 4510 | |||
202009010012300627 | 9/1/2020 | 2:13:29 PM | 22G1 | ||||
202009010012300647 | 9/1/2020 | 2:42:00 PM | 22G1 | ||||
202009010012300648 | 9/1/2020 | 2:42:00 PM | 22G1 | ||||
202009010012300651 | 70820154602 | 9/1/2020 | 2:43:05 PM | 22G1 | |||
202009010012300652 | 70820154602 | 9/1/2020 | 2:43:05 PM | 22G1 | |||
202009010012300653 | 9/1/2020 | 2:44:54 PM | 22G1 | ||||
202009010012300654 | 9/1/2020 | 2:44:54 PM | 22G1 | ||||
202009010012300669 | 9/1/2020 | 3:00:56 PM | 22G1 | ||||
202009010012300670 | 9/1/2020 | 3:00:56 PM | 22G1 | ||||
202009010012300672 | 9/1/2020 | 3:01:57 PM | 22G1 | ||||
202009010012300673 | 9/1/2020 | 3:03:01 PM | 2210 | ||||
202009010012300674 | 9/1/2020 | 3:03:01 PM | 22G1 | ||||
202009010012300680 | 9/1/2020 | 3:10:36 PM | 22G1 | ||||
202009010012300681 | 9/1/2020 | 3:10:36 PM | 22G1 | ||||
202009010012300685 | 40820181663 | 9/1/2020 | 3:05:43 PM | 22G1 | |||
202009010012300686 | 40820181663 | 9/1/2020 | 3:05:43 PM | 22G1 | |||
202009010012300689 | 40820149684 | 9/1/2020 | 3:29:20 PM | 22G1 | |||
202009010012300690 | 40820149684 | 9/1/2020 | 3:29:20 PM | 22G1 | |||
202009010012300699 | 9/1/2020 | 3:40:03 PM | 22G1 | ||||
202009010012300702 | 9/1/2020 | 3:40:03 PM | 22G1 | ||||
202009010012300709 | 40820180729 | 9/1/2020 | 3:46:43 PM | 22G1 | |||
202009010012300710 | 40820180729 | 9/1/2020 | 3:46:43 PM | 22G1 | |||
202009010012300717 | 70820135967 | 9/1/2020 | 3:51:35 PM | 22G1 | |||
202009010012300718 | 70820135967 | 9/1/2020 | 3:51:35 PM | 22G1 | |||
202009010012300729 | 40820160072 | 9/1/2020 | 4:00:57 PM | 22G1 | |||
202009010012300730 | 40820160072 | 9/1/2020 | 4:00:57 PM | 22G1 | |||
202009010012300737 | 40820181663 | 9/1/2020 | 4:04:21 PM | 22G1 | |||
202009010012300738 | 40820181663 | 9/1/2020 | 4:04:21 PM | 22G1 | |||
202009010012300739 | 9/1/2020 | 3:47:54 PM | 2210 | ||||
202009010012300740 | 9/1/2020 | 3:47:54 PM | 2210 | ||||
202009010012300743 | 9/1/2020 | 4:10:45 PM | 22G1 | ||||
202009010012300744 | 9/1/2020 | 4:10:45 PM | 22G1 | ||||
202009010012300755 | 9/1/2020 | 4:12:40 PM | 22G1 | ||||
202009010012300756 | 9/1/2020 | 4:12:40 PM | 22G1 | ||||
202009010012300793 | 9/1/2020 | 4:47:28 PM | 22G1 | ||||
202009010012300794 | 9/1/2020 | 4:47:28 PM | 22G1 | ||||
202009010012300811 | 9/1/2020 | 4:51:46 PM | 22G1 | ||||
202009010012300819 | 40820178751 | 9/1/2020 | 5:00:18 PM | 22G1 | |||
202009010012300820 | 40820178751 | 9/1/2020 | 5:00:18 PM | 22G1 | |||
202009010012300821 | 9/1/2020 | 5:03:21 PM | 22G1 | ||||
202009010012300822 | 9/1/2020 | 5:03:21 PM | 22G1 | ||||
202009010012300829 | 70820176411 | 9/1/2020 | 5:10:16 PM | 22G1 | |||
202009010012300830 | 70820176411 | 9/1/2020 | 5:10:16 PM | 22G1 | |||
202009010012300835 | 40820160072 | 9/1/2020 | 5:13:53 PM | 22G1 | |||
202009010012300837 | 40820178751 | 9/1/2020 | 5:16:48 PM | 22G1 | |||
202009010012300838 | 40820178751 | 9/1/2020 | 5:16:48 PM | 22G1 | |||
202009010012300839 | 40820160072 | 9/1/2020 | 5:18:23 PM | 22G1 | |||
202009010012300840 | 40820160072 | 9/1/2020 | 5:18:23 PM | 22G1 | |||
202009010012300845 | 40820178751 | 9/1/2020 | 5:22:55 PM | 22G1 | |||
202009010012300846 | 40820178751 | 9/1/2020 | 5:22:55 PM | 22G1 | |||
202009010012300857 | 40820184266 | 9/1/2020 | 5:29:25 PM | 2210 | |||
202009010012300858 | 40820184266 | 9/1/2020 | 5:29:25 PM | 2210 | |||
202009010012300895 | 40820184264 | 9/1/2020 | 5:39:11 PM | 2210 | |||
202009010012300896 | 40820184264 | 9/1/2020 | 5:39:11 PM | 2210 | |||
202009010012300897 | 40820149684 | 9/1/2020 | 5:40:18 PM | 22G1 | |||
202009010012300898 | 40820149684 | 9/1/2020 | 5:40:18 PM | 22G1 | |||
202009010012300903 | 70820135967 | 9/1/2020 | 5:42:48 PM | 22G1 | |||
202009010012300904 | 70820135967 | 9/1/2020 | 5:42:48 PM | 22G1 | |||
202009010012300927 | 40820133973 | 9/1/2020 | 6:11:57 PM | 22G1 | |||
202009010012300928 | 40820133973 | 9/1/2020 | 6:11:57 PM | 22G1 | |||
202009010012300931 | 40820178751 | 9/1/2020 | 6:14:46 PM | 22G1 | |||
202009010012300932 | 40820178751 | 9/1/2020 | 6:14:46 PM | 22G1 | |||
202009010012300967 | 40820162447 | 9/1/2020 | 7:42:33 PM | 22G1 | |||
202009010012300968 | 40820162447 | 9/1/2020 | 7:42:33 PM | 22G1 | |||
202009010012300969 | 40820184264 | 9/1/2020 | 7:43:29 PM | 2210 | |||
202009010012300970 | 40820184264 | 9/1/2020 | 7:43:29 PM | 2210 | |||
202009010012300974 | 40820184250 | 9/1/2020 | 7:46:17 PM | 2210 | |||
202009010012300975 | 40820184250 | 9/1/2020 | 7:46:17 PM | 2210 | |||
202009010012300977 | 40820163353 | 9/1/2020 | 7:45:07 PM | 2210 | |||
202009010012300978 | 40820163353 | 9/1/2020 | 7:45:07 PM | 2210 | |||
202009010012300985 | 70820142419 | 9/1/2020 | 7:52:09 PM | 2210 | |||
202009010012300986 | 70820142419 | 9/1/2020 | 7:52:09 PM | 2210 | |||
202009010012300988 | 70820160645 | 9/1/2020 | 7:52:38 PM | 22G1 | |||
202009010012300990 | 40820184500 | 9/1/2020 | 7:53:27 PM | 45G1 | |||
202009010012301003 | 40820133973 | 9/1/2020 | 8:18:36 PM | 22G1 | |||
202009010012301004 | 40820133973 | 9/1/2020 | 8:18:36 PM | 22G1 | |||
202009010012301005 | 40820149680 | 9/1/2020 | 8:19:38 PM | 22G1 | |||
202009010012301006 | 40820149680 | 9/1/2020 | 8:19:38 PM | 22G1 | |||
202009010012301007 | 40820184250 | 9/1/2020 | 8:20:38 PM | 2210 | |||
202009010012301008 | 40820184250 | 9/1/2020 | 8:20:38 PM | 2210 | |||
202009010012301015 | 40820155968 | 9/1/2020 | 8:21:50 PM | 2210 | |||
202009010012301016 | 40820155968 | 9/1/2020 | 8:21:50 PM | 2210 | |||
202009010012301029 | 40820160072 | 9/1/2020 | 8:42:47 PM | 22G1 | |||
202009010012301030 | 40820160072 | 9/1/2020 | 8:42:47 PM | 22G1 | |||
202009010012301035 | 40820149680 | 9/1/2020 | 8:54:20 PM | 22G1 | |||
202009010012301036 | 40820149680 | 9/1/2020 | 8:54:20 PM | 22G1 | |||
202009010012301041 | 40820184250 | 9/1/2020 | 8:53:12 PM | 2210 | |||
202009010012301042 | 40820184250 | 9/1/2020 | 8:53:12 PM | 2210 | |||
202009010012301045 | 40820149680 | 9/1/2020 | 8:59:44 PM | 22G1 | |||
202009010012301046 | 40820149680 | 9/1/2020 | 8:59:44 PM | 22G1 | |||
202009010012301047 | 40820184250 | 9/1/2020 | 8:56:04 PM | 2210 | |||
202009010012301048 | 40820184250 | 9/1/2020 | 8:56:04 PM | 2210 | |||
202009010012301049 | 40820133973 | 9/1/2020 | 9:01:40 PM | 22G1 | |||
202009010012301050 | 40820133973 | 9/1/2020 | 9:01:40 PM | 22G1 |
Thanks in advance
D