Posts by X10A

Re: SumIf By Cell Color

Hey, this is good!

Re: Cumulative Total By Month

Hi PCI,

Thank you very much!

Re: Cumulative Total By Month

Hi PCI,

Thank you. Got me curious where the field "month" came from but I guess that is another question to start a new thread.

Regards.

Re: Cumulative Total By Month

Hi Dave,

As much as i would like to, I'm afriad I can't. I have to protect my company's interests.

Bob's solution worked just fine. (Thanks again, Bob!)

Re: Cumulative Total By Month

Hi Dave,

Would it be too troublesome for you to show me how can I achieve it with a pivot table?

Regards.

Re: Cumulative Total By Month

Hi Bob,

Thank you. It was a nice one!

Regards.

Hi,

I have a list of daily sales which I would like to have a cumulative total by month. Once the next month is encountered, the cumulative total will reset for that month again.

E.g. (please see attached file)
Column C is my cumulative total. The cumulative sum will reset when the month changes from Jan to Feb.

I can achieve the results using VBA but I need to distribute my report to parties whose VBA environment is disabled. So, I need to work around this with a formula.

Regards.

Re: No Right Mouse Click

Not sure if this helps.

Set the macro security to High and uncheck the "Trust all installed add-ins and template" and "Trust access to Visual basic project". I'm using Excel 2003 so I'm not sure if the options I mentioned are also presented in Excel 2002.

Re: Environ Command In Vba To Get Logged Windows User Name

Thanks. Haven't got a need to use it but sure will come in handy when the time comes!

Re: Open Workbook With ShellExecute

Hi Fencliff,

Your code is working great! Thanks!

Regards.

Re: Open Workbook With ShellExecute

Thanks, Andy. It worked.

Just a slight issue. Can I open 2008.xls in an exisitng Excel window instead of opening another instance of Excel again?

Re: Open Workbook With ShellExecute

I am trying to find a method that I can use to open any file types. Then I came across the ShellExecute which seem like the ideal solution. So far, it worked with other file types (e.g *.txt) and I thought it should worked for *.xls file as well.

I can use the Workbooks.Open method but I wish to know why ShellExecute cannot be used on xls file. Just thought that it may be interesting to know.

Regards.

Hi,

I am trying to using the following method to open an Excel workbook (2008.xls) which did not work.

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long


Private Sub OpenWB


ShellExecute(0, "Open", "C:\2008.xls", vbNullString, vbNullString, 1) 


End Sub

After running the procedure OpenWB, Excel will halt and the only way I can close Excel is through the windows task manager. By the way, I have applied service pack 3 for Excel.

Thanks for any advice.

Regards.

Re: Extract Pattern From A Cell

Quote from jonny

1. Cell = "golf car is really comfort", pattern = "_ _ _ _" will return "golf"

Hi jonny,

From your example 1, I assume the pattern is the length of characters in a word. I came up with something like this:

If A1 contains "golf car is really comfort", then by entering 4 as the length of word to return, B1 will return "golf"

Sub MatchCharLength()
Dim x As Variant, y() As Variant, Length1 As Integer


x = Split(Cells(1, 1), " ")
Length1 = InputBox("Enter the number of characters in the word to return")
i2 = 0


For i = 0 To UBound(x)
If Len(x(i)) = Length1 Then
ReDim Preserve y(0 To i2)
y(i2) = x(i)
i2 = i2 + 1
End If
Next i


For i = 0 To UBound(y)
Cells(1, i + 2) = y(i)
Next i


End Sub

Re: Cell Accepts 3-digit Numbers Only

Hi iiawak,

This is my suggestion. Apply data validation in cell B1. Choose the "custom" type and under formula, type "=IF(A1="Y",LEN(B1)<=3,LEN(B1)>0)" (without the quotes).

Re: Clear UserForm Controls Button

Hi Reafidy,

Your code comments are simple and clear, making it easy to understand. I have learn something too. Thank you.

Re: Open Time

Maybe I am oversimplifying things but this is how I do it. The following macro will be placed in the Personal.xls which will load everytime Excel opens.

Private Sub Workbook_Open()
Activesheet.Range("A1").Value = Now
End Sub

Re: Lookup Function With Pivot Table

Hi Natalie,

I would normally use GETPIVOTDATA formula if I want to look up values from a pivot table.

Regards,
X10A

Re: Open Csv With Wordpad

Hi Michal,

Maybe you can try this.

1) Manually import the CSV file from Excel.
2) Then apply AutoFilter and filter out all records in column AJ that is <> 5
3) Copy the filtered range and paste to another workbook.
4) Save the new file as CSV type.

See if this works.

Regards,
X10A

Re: Open Csv With Wordpad

Michal,

This may not be what you are looking for but if your CSV cannot be edited in Excel, have you tried editing it in MS Word instead?

Regards,
X10A