Changing two cells in multiple files

[jhightowerisc]

Hello everyone

I want to be able to change two cells in hundreds of files.

One cell is M2 and is currently a dropdown box i want to replace with the NPI

The other cell is little different

C1 current has a part number that is in this format 100xxxx i want to be able to make that 1000xxxx without changing the last four digits.


Thanks for listening

 

[ER_Neha]

Hello Please try this & let me know if it works

Sub mycode()

Dim ws As Worksheet

Dim search_x As String
search_x = "x"

Dim x_pos As Byte

Dim cvalue As String
Dim firstpart As String
Dim lastpart As String

For Each ws In ActiveWorkbook.Worksheets
    ws.Range("M2").Value = "npi"
    x_pos = InStr(1, ws.Range("C1").Value, search_x, vbTextCompare)
    cvalue = ws.Range("c1").Value
    firstpart = Mid(cvalue, 1, x_pos - 1)
    lastpart = Mid(cvalue, x_pos, Len(cvalue) - x_pos + 1)
    ws.Range("c1").Value = firstpart & "0" & lastpart
  
Next

End Sub

 

[jhightowerisc]

Hello Please try this & let me know if it works

Sub mycode()

Dim ws As Worksheet

Dim search_x As String
search_x = "x"

Dim x_pos As Byte

Dim cvalue As String
Dim firstpart As String
Dim lastpart As String

For Each ws In ActiveWorkbook.Worksheets
    ws.Range("M2").Value = "npi"
    x_pos = InStr(1, ws.Range("C1").Value, search_x, vbTextCompare)
    cvalue = ws.Range("c1").Value
    firstpart = Mid(cvalue, 1, x_pos - 1)
    lastpart = Mid(cvalue, x_pos, Len(cvalue) - x_pos + 1)
    ws.Range("c1").Value = firstpart & "0" & lastpart
  
Next

End Sub

Will this open every excel file in a folder and change each file?

 

[ER_Neha]

Will this open every excel file in a folder and change each file?

This code has to be modified for that purpose. I wrote this for a single workbook ( excel file) containing multiple sheets having your criteria.

 

[jhightowerisc]

This code has to be modified for that purpose. I wrote this for a single workbook ( excel file) containing multiple sheets having your criteria.

I can get it to change the M2 to NPI so that works but the C1 adding the 0 isn't working.

It says Run-time error '5':
Invalid procedure call or argument

 

[ER_Neha]

I can get it to change the M2 to NPI so that works but the C1 adding the 0 isn't working.

It says Run-time error '5':
Invalid procedure call or argument

Does your cell c1 contain "100xxx" as value ? And is it a string ? Can you please share the error you are getting.

Secondly do you need the code for all the file in the folder ?

 

[jhightowerisc]

Does your cell c1 contain "100xxx" as value ? And is it a string ? Can you please share the error you are getting.

Secondly do you need the code for all the file in the folder ?

Thanks for you help btw

So the numbers very like 1004565 or 1005644 they are just part numbers- we extended the range that why its now 10004565 10005644

Also yeah i could use the code to change all the files in a folder


I just changed the search_x = "0"
and it does what i need

So now it changes both but it does throw an error after it changes both cells

 

[ER_Neha]

Thanks for you help btw

So the numbers very like 1004565 or 1005644 they are just part numbers- we extended the range that why its now 10004565 10005644
View attachment 31156

Also yeah i could use the code to change all the files in a folder


I just changed the search_x = "0"
and it does what i need

So now it changes both but it does throw an error after it changes both cells

please try this code again . and later i can help you with multiple files in one folder

Sub mycode()

Dim ws As Worksheet
Dim i As Byte
Dim search_x(0 To 8) As String
For i = o To 8
    search_x(i) = i + 1
Next i

Dim x_pos As Byte

Dim cvalue As String
Dim firstpart As String
Dim lastpart As String
On Error GoTo leave

For Each ws In ActiveWorkbook.Worksheets
    ws.Range("M2").Value = "npi"
    If Not IsEmpty(ws.Range("c1").Value) Then
        i = 0
        Do While x_pos = 0
            x_pos = InStr(2, ws.Range("C1").Value, search_x(i), vbTextCompare)
           i = i + 1
           Loop
            
        cvalue = ws.Range("c1").Value
        firstpart = Mid(cvalue, 1, x_pos - 1)
        lastpart = Mid(cvalue, x_pos, Len(cvalue) - x_pos + 1)
        ws.Range("c1").Value = firstpart & "0" & lastpart
     End If
Next
leave:
End Sub

 

[jhightowerisc]

please try this code again . and later i can help you with multiple files in one folder

Sub mycode()

Dim ws As Worksheet
Dim i As Byte
Dim search_x(0 To 8) As String
For i = o To 8
    search_x(i) = i + 1
Next i

Dim x_pos As Byte

Dim cvalue As String
Dim firstpart As String
Dim lastpart As String
On Error GoTo leave

For Each ws In ActiveWorkbook.Worksheets
    ws.Range("M2").Value = "npi"
    If Not IsEmpty(ws.Range("c1").Value) Then
        i = 0
        Do While x_pos = 0
            x_pos = InStr(2, ws.Range("C1").Value, search_x(i), vbTextCompare)
           i = i + 1
           Loop
            
        cvalue = ws.Range("c1").Value
        firstpart = Mid(cvalue, 1, x_pos - 1)
        lastpart = Mid(cvalue, x_pos, Len(cvalue) - x_pos + 1)
        ws.Range("c1").Value = firstpart & "0" & lastpart
     End If
Next
leave:
End Sub

Looks good except it's adding the 0 after the first number instead of before. so it's doing 10070245 vs 10007245

 

[ER_Neha]

Looks good except it's adding the 0 after the first number instead of before. so it's doing 10070245 vs 10007245

Hello dear thank you for providing the feedback, I am also a learner, I checked with input you gave, that was an error, so I changed my code and and providing you with the new one , I hope it works for you, please check and provide the feedback

Sub mycode()

Dim ws As Worksheet
Dim i As Integer
Dim search_x() As String

Dim cvalue As String
Dim firstpart As String
Dim lastpart As String
On Error GoTo leave

For Each ws In ActiveWorkbook.Worksheets
    ws.Range("M2").Value = "npi"
    ReDim search_x(1 To Len(ws.Range("c1").Value) - 1)
    For i = 1 To Len(ws.Range("c1").Value) - 1
     search_x(i) = Mid(ws.Range("c1").Value, i + 1, 1)
     Next i
     For i = 1 To Len(ws.Range("c1").Value) - 1
        If search_x(i) <> "0" Then
            cvalue = ws.Range("c1").Value
            firstpart = Mid(cvalue, 1, i)
            lastpart = Mid(cvalue, i + 1, Len(cvalue) - i)
        ws.Range("c1").Value = firstpart & "0" & lastpart
        Exit Sub
        End If
    Next i
Next
leave:
End Sub