Posts by ekma234

Hello,

i'm trying to align grid lines of 3 separate Excel charts as seen on image (i'm new to charts), representing Actual sale by products and % variance to Plan/Prior Year.

Manually i cannot align them to the exact alignment (check the last grid lines below):

[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Variance chart.PNG","data-attachmentid":1213981}[/ATTACH]
Is it possible to align grid lines just by setting the correct dimensions of chart/plot area?

Is there any better way to achieve the same result ? maybe by combining all 3 charts using a combo chart?

I've used charts originally created & posted on xelplus.com.

Many thanks & best regards,
Matjaž

Hi Kenneth,

this is great. Due to the IF condition, the code now works only on nonempty cells.

As for the test results, now all the previously mentioned blank rows stay unchanged (without zero values).

Thank you again, i appreciate it.:thanx:

Thank you Kenneth Hobson,
i'm excited to tell you that the macro works excellent! :congrats:

FYI, the error was 13.

You are probably correct also with your statement:

Quote

You might want to add a line to skip if the value ="". Otherwise, "" * 1 = 0.

Before testing i have manually added a blank row to the file, just to test the behaviour.

Additionally there are empty cells included in the sample source file (not part of manually added blank row), which represent a zero value, but cells do not include any value.

After applying the macro, the following happens:
- cells, part of blank rows, result with 0 values (change from blank to "0").
- empty cells (not part of blank row) result as empty cells (no change).

Is there a reason why Excel interprets some empty/blank cells as "" and some not ""?

Changing the number format does not result in geting a zero value into a blank cell

Thank you Carim,
i've just tested and i get "Run-time error 13 Type mismatch" at the following line:

ReDim Preserve myArr(1 To 8, 1 To R)

I've also tried to select the specific range at "' Load Array with your Calculation" - is that necessary?

Is there a reason why the C&R are switched in the following line:

myArr(C, R) = .Cells(R, C) * 1

Thanks,
BR

Hi,

i'm trying to create a VBA macro to convert an accounting general ledger exported as Text to Numbers.

I found the only useful way at the link but the macro is written for 1-dimensional array only (1 column only)

Sub texttoNum3() 'Excel VBA dynamic example of the text to numbers procedure.
Dim ar As Variant
Dim var As Variant
Dim i As Integer


ar=Range("a11", Range("A" & Rows.Count).End(xlUp))


ReDim var(1 To UBound(ar), 1 To 1)


For i=1 To UBound(ar) 'Start of VBA loop
var(i, 1)=ar(i, 1) * 1
Next i
Range("C11:C" & UBound(ar) + 10)=var


End Sub

I'd like to use it for a table source (i.e. 18 rows & 8 columns), which means 2-dimensions. I've customised the code (pls. see below), but i get an error (see ERROR):

Sub texttoNum2D()
Dim ar As Variant
Dim var As Variant
Dim i As Integer
Dim j As Integer


ar = Range("a1", Range("H" & Rows.Count).End(xlUp))      ' 2-D array from column A to H


ReDim var(1 To UBound(ar), 1 To UBound(ar, 2))


For i = 1 To UBound(ar)       'Start of VBA loop 
For j = 1 To UBound(ar, 2)
var(i, j) = ar(i, j) * 1              'multiply all cells by 1 => ERROR
Next j
Next i


'Returning Values of the array:
Range("A1:H" & UBound(ar)) = var    


End Sub

I've checked the looping using below code, results in the Immediate window were fine - all cells included:

Debug.Print "(" & i & "," & j & ")", var(i, j)

I apologize, but i cannot provide an example file.

Pls. help