Converting values from MM to Inches in multiple cells and multiple workbooks

[KyleRichJohn]

I am new and have never worked with macros. I have 30 different workbooks with cells that have their values in MM and I want to convert all of them to Inches. I will eventually have to do this with a few hundred workbooks.

All the workbooks have the cells that need to be converted in the same locations. There are 4 worksheets within these workbooks but I only need to change the values in one worksheet. I was hoping I would be able to make a macro so I could open the workbook, insert the macro, run the macro, and resave the workbook with the new values.

I have been doing some research into this online and I feel I am becoming more lost than when I started.

The cell locations are contained in a worksheet titled "PSDS"

The cell locations are: E32, G32, K32, Q32, S32, U32, F47, H47, J47, Q47, S47, U47.

=sum(PSDS!E32,PSDS!G32,PSDS!K32,PSDS!Q32,PSDS!S32,PSDS!U32,PSDS!F47,PSDS!H47,PSDS!J47,PSDS!Q47,PSDS!S47,PSDS!U47

Also, does it matter that some of the cells might not have any value in there?

Thank

 

[Veritan]

This should do the trick.

Option Explicit
Public Const ConversionRate = 25.4
Private Sub Conversion()
    Dim r As Range, rng As Range, Values(1 To 12) As Double
    Dim i As Integer
    
    Set rng = Sheets("PSDS").Range("E32,G32,K32,Q32,S32,U32,F47,H47,J47,Q47,S47,U47")
    i = 1
    For Each r In rng.Cells
        Values(i) = r / ConversionRate
        i = i + 1
    Next r
    
    i = 1
    For Each r In rng.Cells
        r = Values(i)
        i = i + 1
    Next r
    
    'This next part is only necessary if you want to get rid of the 0's that
    'display if a cell has no value in it.
    
    i = 1
    For Each r In rng.Cells
        If r = 0 Then r = "" 'This can be changed to any other value if needed.
    Next r
End Sub

Keep in mind, I only did a quick Google search for the conversion rate, yours may need to be more precise, depending on your needs. Also, the last subsection of the code can be deleted if you're okay with 0's showing up when your cells don't have a value in them. Currently, the code will just delete the contents of the cell if a 0 is returned. However, you can change it to say something like "No original value found" if you wanted. Be sure to insert a module first and then put the code in the module. Don't put it in the code area for a specific sheet.

 

[JLGWhiz]

Hi KyleRichJohn, welcome to the forum.
If the workbooks are all in one directory and are the only workbooks in that directory, AND the workbook which hosts the macro is in that same directory, then you could use this procedure.

Sub mmToInch()
Dim wb As Workbook, sh As Worksheet, fPath As String, fName As String, mmAry As Variant, i As Long
fPath = ThisWorkbook.Path
If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
fName = Dir(fPath & "*.xl*")
        Do While fName <> ""
            If fName <> ThisWorkbook.Name Then
                Set wb = Workbooks.Open(fPath & fName)
                On Error Resume Next
                Set sh = Sheets("PSDS")
                    If Err.Number = 9 Then
                        MsgBox "No Sheet 'PSDS' found for " & wb.Name
                        On Error GoTo 0
                        GoTo SKIP:
                    End If
                On Error GoTo 0
                With sh
                 mmAry = Array("E32", "G32", "K32", "Q32", "S32", "U32", "F47", "J47", "Q47", "S47", "U47")
                    For i = LBound(mmAry) To UBound(mmAry)
                        .Range(mmAry(i)) = .Range(mmAry(i)).Value / 25.4
                    Next
                End With
SKIP:
                wb.Close True
            End If
            fName = Dir
        Loop
End Sub

If you are not familiar with macros, then Do these steps.
1. Make sure your Security Center settings allow you to access VBA.
2. Make sure your Security level is set to allow macros to run.
3. Save the workbook which will host the code as a Macro Enabled Workbook (.xlsm)
4. To install the code, open the VBA Editor by pressing Alt + F11. If the large pane is dark, then click Insert>Module. When the large pane brightens, copy the code above and paste it into that code pane.
5. Close the VB editor window and press Alt + F8 which will display the Macro dialog box.
6. You can double click the macro you want to run, or left click it and then click Run to execute the macro.

If an error occurs, click the debug button and post back with the error message and line of code that was highlighted when you clicked the debug button.

 

[Veritan]

I was working around with the code and found a more effective way of running it. Try this instead.

Option Explicit
Public Const ConversionRate = 25.4
Private Sub Conversion()
    Dim r As Range, rng As Range, Values(1 To 12) As Double
    Dim i As Long
    
    Set rng = Sheets("PSDS").Range("E32,G32,K32,Q32,S32,U32,F47,H47,J47,Q47,S47,U47")
    i = 1
    For Each r In rng.Cells
        Values(i) = r / ConversionRate
        r = Values(i)
        If r = 0 Then r = "" 'This line can be changed or eliminated if you want.
        i = i + 1
    Next r
End Sub

What JLGWhiz suggested is a great idea too, if you want to do all the workbooks at one go.

 

[KyleRichJohn]

Thank you JLGWhiz and Veritan. You have saved the day. I am very grateful for your help.

Another question I would like to follow up with is do you have any recommendation as to where or how I should slowly start to learn macros/vb. I love using excel because of how powerful and useful it is and this just opened up a whole new world for me.

I usually will find a spreadsheet that does something similar to what I am working with and reverse engineer the process and just play around with it. But this seems like I will need to learn a new language.

 

[Veritan]

It's actually easier than you think. My go-to place for how to learn VBA for Excel is Wise Owl. They have a tremendous playlist on YouTube that covers all the basics and a good number of advanced concepts as well. The guy who narrates them is a really cool British guy, which helps when he's covering some very technical aspects of coding! Here's the link for the first video in the series. This is how I taught myself coding. Once I had finished the videos, I just kept searching around the internet. It's amazing what you can find Hope you enjoy learning VBA, it's a lot of fun!

https://www.youtube.com/watch?v=KHO5NIcZAc4&list=PLNIs-AWhQzckr8Dgmgb3akx_gFMnpxTN5

 

[JLGWhiz]

To really learn how to write code, I would suggest that you set up a project (real or imagined) consisting of a process that requires construction of a data base, input of new data, search and retrieval of existing data and production of some type of output (form, email, letter). Then do a block diagram of how you want the process to flow from step 1 to final step. You can then write individual maacros to accomplish the steps and use a master macro to run the indvidual ones as a program. To write the macros, you can use on line tutorials, buy a book for VBA or VB Script programming, visit the forums on line (there are several) and use the VBA and Excel help files to understand what the code is doing. Learning to use the VBA help files is probably one of the most helpful assets you can develop. The current on-line files have more than just the MSN technical description to choose from, although the technical descriptions are usually where I look first because they give the syntax and many of them have example code to show how it is used. But by working on a project, you get to use many different programming tools and can experiment with UserForms and the various Active-X controls that a UserForm can host. Also, get to know how Excel is organized in terms of Workbook, sheets, forms and controls. That is an imperative when you write the code. You must understand the Excel capabilities, limitations and ideosyncrasies. Otherwise you will spend many hours debugging code that you write.