Search code examples
excelvbafind

Find value in sheet1, copy offset value, find offset value in sheet 2, paste text in offset cell

I have looked at a lot of different ways to do this and tried to incorporate their ideas into this sub but I keep getting different errors every time I run it. I am searching for "0" in the listed ranges of the STATUS SHEET and finding the offset value which is the vehicle number. Then finding that vehicle number on LRV ISSUES and pasting "OOS" one cell to the right. It seems to work once and then errors so I am thinking my looping is wrong.

Thanks for any help. EDIT TO INCLUDE SUGGESTIONS AND CLEAN UP

Sub FindOOS()
    Dim c As Range
    Dim d As Range
    Dim rng As Range
    Dim firstAddressC As String
    Dim firstAddressD As String
    Dim lrv As String
    Dim sht1 As Worksheet
    Dim sht2 As Worksheet
    Set sht1 = ThisWorkbook.Worksheets("STATUS SHEET")
    Set sht2 = ThisWorkbook.Worksheets("LRV ISSUES")
    
    With sht1.Range("B5:B37,E5:E37,H5:H37,K5:K37,M5:M35")
        Set c = .Find("0", LookIn:=xlValues)
        If Not c Is Nothing Then
            firstAddressC = c.Address
            Do
                lrv = c.Offset(, -1).Value
                With sht2.Range("A3:R32")
                    Set d = .Find(lrv, LookAt:=xlWhole)
                    If Not d Is Nothing Then
                        d.Offset(, 1).Value = "OOS"
                    End If
                End With
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstAddressC
        End If
    End With
End Sub

STATUS SHEET MD

101 1 134 1 167 0 SHOP/NWROF 200 2C OOS/CROF/NO RUN 233 2N PMI NW NIS 1R
102 3 NIS 135 0 SHOP 168 0 SHOP/NWROF 201 1 234 2C OOS/CROF/NO RUN
103 1 136 2C BODY SHOP 169 2N OOS/NW/NO RUN/WHEELS 202 1 235 2C Gear Box
104 1R A coupled restriction/TSS 137 0 SHOP 170 2C OOS/CROF/NO RUN/THRESHOLD 203 0 SHOP 236 1
105 3 NIS 138 2C OOS/CROF/NO RUN 171 1 204 2C PMI CROF 237 1
106 3 NIS 139 0 SHOP 172 0 SHOP 205 2C PMI CROF 238 1 WHEELS
107 3 NIS 140 0 SHOP 173 1 WHEELS 206 1 239 1
108 0R SHOP/A COUPLED 141 1 174 2N PMI NW 207 0 SHOP 240 2N NIS/NW
109 0R A COUPLED/B shear bolt/SHOP 142 1 175 2N NIS/NW 208 2R A COUPLED/TIRE X-CHANGE 241 1
110 1 143 0 SHOP 176 1 209 1 242 2C TIRE X-CHANGE
111 1 144 1 177 3 Engineering 210 2C PMI CROF 243 2N TRANSPORTATION DOWNLOAD
112 2C OOS/CROF/NO RUN 145 1 178 2C OOS/CROF/NO RUN/WHEELS 211 1 244 1
113 2N PMI NW 146 1 179 2C SHOP 212 1 245 1
114 1 147 1 180 2R B COUPLED/RCS BODY SHOP 213 2C PMI CROF 246 1
115 1 148 2C OOS/CROF/NO RUN 181 1 214 1 247 1
116 1 149 2C OOS/CROF/NO RUN /shop 182 1 215 1 248 1
117 2C OOS/CROF/NO RUN 150 2C PMI CROF 183 1 216 1 249 1
118 1 151 0 SHOP/NWROF 184 1 217 2C OOS/CROF/NO RUN 250 1
119 1 152 0 SHOP 185 1 218 2N OOS/NW/NO RUN 251 1 WHEELS
120 1 153 1 186 1 219 1 252 0 SHOP
121 1 WHEELS 154 2C BODY SHOP 187 2C RCS/AIRLINE 220 1 253 2N OOS/NW/NO RUN
122 1 155 1 188 1 221 2N PMI NW 254 1
123 1 156 1 189 1 222 2C OOS/CROF/NO RUN 255 1
124 2C Air spring 157 3 NIS 190 2C OOS/CROF/NO RUN/Body shop 223 0 SHOP 256 0 SHOP
125 1 158 1 191 0 SHOP/NWROF 224 1 257 1
126 0 SHOP 159 2C OOS/CROF/NO RUN/SHOP 192 1 225 1 258 1
127 1 160 1 193 1 226 3 NIS 259 1
128 1 161 2C OOS/CROF/NO RUN 194 0 SHOP/NWROF 227 0 SHOP 260 1 NIS/ 4L motor
129 2C RCS/Axle 2, Wire short 162 2C PMI CROF 195 2N BODY SHOP/SHOP 228 1 261 1
130 0 SHOP 163 1 196 2C OOS/CROF/NO RUN 229 0R SHOP/A COUPLED/Radio 262 1
131 1 164 0 SHOP 197 0 SHOP 230 1 263 1
132 1 165 1 198 1 231 1 WHEELS STREET CAR
133 2C OOS/CROF/NO RUN 166 0 SHOP 199 1R B COUPLED 232 1

LRV ISSUES MD

LRV DATE CAMS LRV DATE CAMS LRV DATE CAMS LRV DATE CAMS LRV DATE CAMS LRV DATE CAMS
101 45420 Ø 131 45417 Ø M5 161 45384 Ø 191 45420 Ø 221 45418 11 251 45397 SAT
102 NIS NIS 132 SAT 162 45413 6 10 11 192 45390 Ø 222 45406 Ø 252 45415 Ø
103 45419 D5 133 45384 Ø 163 45417 Ø 193 45413 SAT 223 45415 Ø 253 45399 8 9 CUT7
104 45417 SAT 134 45418 Ø 164 45378 Ø 194 45420 Ø 224 45418 Ø 254 45415 SAT
105 NIS NIS 135 45407 Ø 165 45417 5 195 45415 Ø 225 45392 *NOTE 255 45403 H2O 6
106 NIS NIS 136 45399 Ø 166 45398 Ø 196 45419 Ø 226 NIS NIS 256 45406 7 10
107 NIS NIS 137 45393 Ø 167 45417 Ø 197 45415 Ø 227 45406 8 9 11 257 45420 Ø
108 SAT 138 45418 Ø 168 45415 Ø 198 45419 Ø 228 45397 Ø 258 45411 Ø
109 45417 SAT 139 45392 Ø 169 45418 Ø 199 45415 Ø 229 45420 Ø 259 45415 Ø
110 45417 10 140 45393 SAT 170 45403 Ø 200 45418 Ø 230 45415 Ø 260 45415 Ø M2
111 45413 Ø 141 45386 Ø 171 45417 3 201 45419 Ø 231 45406 3 261 45417 7 10 D4 D8
112 45371 Ø 142 45419 Ø 172 45413 SAT 202 45420 Ø 232 45417 Ø 262 45417 M3
113 45417 2 143 45418 Ø 173 45411 Ø 203 45420 Ø 233 45420 Ø 263 45417 Ø
114 45386 7 144 45417 Ø 174 45418 6 D9 204 45419 Ø 234 45415 Ø
115 45420 Ø 145 45415 Ø 175 45389 Ø 205 45413 Ø 235 45399 Ø
116 45420 Ø 146 45420 Ø 176 45420 Ø 206 45420 4 236 45406
117 45413 9 147 45413 1 3 177 NIS NIS 207 45406 4 7 11 237 45420 Ø
118 45406 Ø 148 45392 5 D9 178 45392 Ø 208 45344 SAT 238 45420 6 7
119 45418 Ø 149 45370 Ø 179 45399 Ø 209 45417 Ø 239 45419 Ø
120 45393 SAT 150 45419 SAT 180 45367 Ø 210 45393 Ø M10 240 45415 Ø
121 45418 SAT 151 45393 Ø 181 45418 Ø 211 45406 Ø 241 45403 Ø
122 45413 Ø 152 45390 Ø 182 45420 SAT 212 45417 1-7 D10 242 45385 Ø
123 45407 11 153 45415 Ø 183 45417 3 213 45413 SAT 243 45406 D2
124 45390 Ø 154 45391 Ø 184 45420 Ø 214 45415 Ø M5 244 45420 Ø
125 45386 SAT 155 45370 4 185 45408 Ø 215 45406 Ø 245 45392 Ø
126 45417 Ø 156 45420 Ø 186 45415 Ø 216 45385 Ø 246 45417 Ø
127 45415 Ø 157 NIS NIS 187 45389 Ø 217 45397 Ø 247 45417 Ø
128 45419 2 6 9 158 45411 Ø 188 45405 3 8 11 218 45400 Ø 248 45419 Ø
129 45407 4 5 D10 159 45398 Ø 189 45413 2 6 219 45420 Ø 249 45419 Ø
130 45415 Ø 160 45418 Ø 190 45419 Ø 220 45413 7 9 250 45419 *NOTE
Solution

    • It's better to specify the argument LookAt:=xlWhole (assuming you are looking for whole cell matching) in Find method.

    • finding the offset value which is the vehicle number - Use the below code to store the vehicle number in a variable (lrv).

    lrv = c.Offset(, -1).Value

    • Set d = .Find(lrv, LookIn:=xlValues) in your code searched lrv on sheet STATUS SHEET. You need another With.

    • Loop While c.Address <> firstAddressC The condition is necessary to avoid looping search.

    Option Explicit
Sub FindOOS()
    Dim c As Range
    Dim d As Range
    Dim firstAddressC As String
    Dim firstAddressD As String
    Dim lrv As String
    
    With Worksheets("STATUS SHEET").Range("B5:B37,E5:E37,H5:H37,K5:K37,M5:M35")
        Set c = .Find("0", LookIn:=xlValues, LookAt:=xlWhole)
        If Not c Is Nothing Then
            firstAddressC = c.Address
            Do
                lrv = c.Offset(, -1).Value  ' **
                With Worksheets("LRV ISSUES").Range("A3:R32")
                    Set d = .Find(lrv, LookIn:=xlValues, LookAt:=xlWhole)
                    If Not d Is Nothing Then
                        d.Offset(, 1).Value = "OOS"
                    End If
                End With
                Set c = .Find("0", After:=c, LookIn:=xlValues, LookAt:=xlWhole)
'                Debug.Print c.Address
            Loop While c.Address <> firstAddressC
        End If
    End With
End Sub

    btw, it's worth to read.

    How to avoid using Select in Excel VBA