-1

I have a table in an excel spreadsheet. In this table I would like to go from columns AP2 : AW2 and run down the rows to the end of the table and perform 2 actions. One I would like to convert the cell values from text to number. Second I would divide each cell by 100. So far my code contains two macros that trigger when hitting Ctrl n. They work but is hardcoded to row 2000 rather than finding the end of the table (sometimes the table could have more or less than 2000 lines). I also don't think having two macros is probably all that efficient. Any help on this would be greatly appreciated!

Sub Number_Conversion()
'Convert text to number
With Sheet1.Range("AP2:AW2000")
    .NumberFormat = "Number"
    .Value = .Value
End With
End Sub

Sub Divide_By()
'Divide number by 100
'declare variables
Dim ws As Worksheet
Dim rng As Range
Dim myVal As Range

Set ws = Sheet1
Set rng = ws.Range("AP2:AW2000")

For Each myVal In rng
    If myVal.Value > 0 Then
        myVal = myVal.Value / 100
    End If
Next myVal
End Sub
Dan Kirchner
  • 45
  • 6
  • 4
    Can you show us the code you have so far? – cybernetic.nomad May 18 '23 at 20:48
  • 2
    This would be a straightforward process. As @cybernetic.nomad pointed out, if you show us your code we'll try to help you fix it. SO is really not about creating code to order. – RichardCook May 18 '23 at 22:38
  • 1
    As soon as I get back to my computer I can certainly provide the code I have to this point. I apologize if it came off that I was ordering code as this is never my intention. I learn so much from the community and look forward to working this with you all very shortly. – Dan Kirchner May 19 '23 at 00:24
  • 1
    I edited the post to include my current code. However I made some formatting mistakes so it's under review. Guess that what I get for working to fast. I hope it gets approved and again I apologize if I put off the wrong impression initially. – Dan Kirchner May 19 '23 at 01:20
  • 2
    You might be interested in [this](https://stackoverflow.com/questions/11169445/find-last-used-cell-in-excel-vba) question. You can also merge the two macros basically cut-copying the `Number_Conversion` content at the end of `Divide_By` and changing `With Sheet1.Range("AP2:AW2000")` into `With rng`. – Evil Blue Monkey May 19 '23 at 08:31
  • You might also consider using [arrays instead of ranges](https://learn.microsoft.com/en-us/office/vba/excel/concepts/excel-performance/excel-tips-for-optimizing-performance-obstructions#read-and-write-large-blocks-of-data-in-a-single-operation) and [Range.Value2 instead of Range.Value](https://learn.microsoft.com/en-us/office/vba/excel/concepts/excel-performance/excel-tips-for-optimizing-performance-obstructions#use-value2-rather-than-value-or-text-when-reading-data-from-an-excel-range) to make your code run faster. – Evil Blue Monkey May 19 '23 at 08:32
  • 1
    I will try it. A courteous person deserves a helping hand. However, I see that the @CLR has just given a seemingly ideal answer, except that he has accidentally written the number with no more flexibility.. If you don't have a better solution, please let me know. – Oscar Sun May 19 '23 at 09:04
  • @EvilBlueMonkey thank you for the tip! With that in mind it looked like I was close without having dynamic row count. – Dan Kirchner May 19 '23 at 14:21

1 Answers1

2

This is how I'd do it:

Sub try_this()
    'declarations
    Dim x As Long, y As Long, myArr, last_row As Long
    
    'find last row in AP:AW range
    last_row = Sheet1.Range("AP:AW").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    'using the range down to last_row
    With Sheet1.Range("AP2:AW" & last_row)
    
    'copy range values to an array
        myArr = .Value2
        
        'cycle through the array horizontally
        For x = 1 To UBound(myArr)
        
            '.. and vertically
            For y = 1 To UBound(myArr, 2)
            
                'divide value by 100 if it equates to more than zero
                If myArr(x, y) > 0 Then myArr(x, y) = myArr(x, y) / 100
                
            Next
        Next
        
        'write array back to the range
        .Value2 = myArr
        
    End With
End Sub

Thanks Oscar for pointing out the part I'd missed. This will now dynamically work on however many rows are required.

CLR
  • 11,284
  • 1
  • 11
  • 29
  • 1
    *"…sometimes the table could have more or less than 2000 lines…"*, so the ```AW2000``` should implement a get function not directly assign. – Oscar Sun May 19 '23 at 08:55
  • 1
    Ah yes, I read that as could be less (which isn't an issue) but didn't appreciate the more part. I'll tweak it. – CLR May 19 '23 at 09:01
  • Good job! It’s just that the conversion of text to numbers has not been implemented, and the value after dividing the original text cell by 100 is directly given, isn’t there any problem? *"… One I would like to convert the cell values from text to number.…"* – Oscar Sun May 19 '23 at 09:38
  • 1
    The conversion is done implicitly by the division. Its true that cells that contain a zero or negative values will remain text - and that could be easily countered - it really depends on the use case here. – CLR May 19 '23 at 10:17
  • 1
    @CLR solution worked like a charm. I appreciate the help and the dialog between everyone. I am going to make it a point to study this code as I see other situations like this popping up for me in the future. Thank you all again for your help! – Dan Kirchner May 19 '23 at 14:16