• 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.

Open an Excel file using a macro

dparteka

dparteka

Member
I'm using the code shown below and it works great. Basically it looks at cell AJ2 to determine which file to open. In AJ2 is a number something like 9642A-001, I'm looking to add a +1 to this number resulting in it looking for 9642A-002... is this possible?

Code: 
Sub OpenSubItem1()
ChDir _
        "H:\Public\PCB-QCR's\SECURITY VIOLATION - CONTACT ADMINISTRATOR IMMEDIATELY"
    Workbooks.Open(Filename:= _
        "H:\Public\PCB-QCR's\SECURITY VIOLATION - CONTACT ADMINISTRATOR IMMEDIATELY\" & Range("AJ2").Text & " QCR.xlsm"" _
        ).RunAutoMacros Which:=xlAutoOpen
End Sub
 
This code only changes the last character i.e. 001, 002, 003, 004, 005, 006, 007, 008, 009.

Code: 
Dim MyNum As Variant
Dim MyTxt As String

MyTxt = Range("AJ2")
MyNum = Right(MyTxt, 1) + 1
Range("AJ2") = Left(MyTxt, Len(MyTxt) - 1) & MyNum
 
Last edited:
Update the code to go from 000 to 999

Code: 
Dim MyNum As Variant
Dim MyTxt As String

MyTxt = Range("AJ2")
MyNum = Right(MyTxt, 3) + 1

If Len(MyNum) = 1 Then
    Range("AJ2") = Left(MyTxt, Len(MyTxt) - 1) & MyNum
ElseIf Len(MyNum) = 2 Then
    Range("AJ2") = Left(MyTxt, Len(MyTxt) - 2) & MyNum
ElseIf Len(MyNum) = 3 Then
    Range("AJ2") = Left(MyTxt, Len(MyTxt) - 3) & MyNum
Else
End If
 
Hi !

Format & Join & Split : ♪ Better, Easier, Faster ! ♫​
Code: 
Sub Demo()
    SP = Split("9642A-001", "-")
    SP(1) = Format$(SP(1) + 1, "000")
    MsgBox Join$(SP, "-")
End Sub
Do you like it ? So thanks to click on bottom right Like !
​
 
Marc L... I like the simplicity of your code and it works as long as the split line has a number in it, when I replace "9642A-001" with "AJ2" which is where the number resides and will change the macro errors out... thank you.
 
I've run into a bit of a problem and didn't foresee this earlier... I need cell AJ2 to return back to its original text. I thought I could just change a plus to a minus in the code but clearly that doesn’t work, sorry but I'm just a grasshopper here.
 
A macro will only store a value as long as it is running. So if you want to return back to the original value, I suggest that you edit the macro I made & add in a line that will store the original value in a different cell at the start before it makes the change. Add something like:

Code: 
Range("A1") = Range("AJ2")
'Rest of my code


Then have a separate macro that will change it back later e.g.
Code: 
Range("AJ2") = Range("A1")
Range("A1").ClearContents
 
You must log in or register to reply here.
Back
Top