Take absolute value of all array elements - Please Help!

[mattreingold]

Hello, below is a small part of my code. my 'loadArr' is the array I am attempting to obtain the absolute values of - I currently get a mismatch error thrown. Any and all help would be much appreciated!


'//
Sub RunReport()


Set WBT = Workbooks("PeelTestReport.xlsm")
Set WPN = WBT.Worksheets("Sheet2")


Dim xlFile As Variant
Dim numberOfFiles As Integer


numberOfFiles = InputBox("Enter Number of Spreadsheets to Open")


Dim fileNames(100) As String
Dim TotalRows As Integer
Dim Counter As Integer
Dim loadArr As Variant
Dim loadArrUsable As Variant
Counter = 0
ChDir "C:\Users\username\Documents\doc name"


For i = 1 To numberOfFiles
' Showing Excel Dialog
xlFile = Application.GetOpenFilename("All Excel Files (*.csv*)," & _
"*.xls*", 1, "Select Excel File", "Open", False)


' Open selected file
Workbooks.Open xlFile

TotalRows = Rows(Rows.Count).End(xlUp).Row
xlFileName = Right(xlFile, 34)
fileNames(i) = xlFileName
loadArr = Abs(Range("B2:B" & TotalRows))



If Counter = 0 Then
WPN.Range("A1:A" & TotalRows - 1) = loadArr
ElseIf Counter = 1 Then
WPN.Range("B1:B" & TotalRows - 1) = loadArr
...
'//

[mattreingold]

Also - I am eventually going to need to be able to shrink this array to values that are greater then ~.05ish, so if you could point me in the right direction there too I'd appreciate it! I'm used to indexing using loops in other languages, however through looking online it looks like this was the only way to grab this array from another workbook, and be able to paste the whole thing to another - yet I don't know how to loop thru or modify values within it. Thanks!

[p45cal]

you will need to do something along the lines of:

loadArr = Range("B2:B" & TotalRows).Value
idx = 0
For i = 1 To UBound(loadArr)
  If Abs(loadArr(i, 1)) >= 0.05 Then
    idx = idx + 1
    loadArr(idx, 1) = Abs(loadArr(i, 1))
  End If
Next i
'now you have loadArr populated with revised values, but only to loadArr(idx,1)
WPN.Range("A1:A" & idx) = loadArr

[mattreingold]

I cannot believe how fast you got back to me with such a clean solution... this worked wonderfully!

I now see how indexing can be used to achieve what I was going for... thanks again!!