Posts by akbarnikain

Hi,

I would like to use a worksheet validation list with the opSort_Single_Column_of_a_Table.xlsmtion "xlAscending, xlDescending" to sort a table column as follows:

Worksheets("data").Range("data").Sort Key1:=Range("b3").Value, _

Order1:=Range("B2").Value, _

Header:=xlYes

However, I get the ERROR message:

"Run-time error'1004':

Sort method of Range class failed"

Would appreciate any help.

PS: I am attaching the workbook

"Sort_Single_Column_of_a_Table"

Hi,

Attached is workbook "Display-All-Matches-from-Search-in-Userform-Wksht"

I have a userform ("frmMain") and when I enter data on the userform to write to the worksheet....as soon as it comes to the code:

With ws


.Range("a" & c).Value = Me.tbFName.Value
.Range("b" & c).Value = Me.tbLName.Value
.Range("c" & c).Value = Me.tbLocation.Value
.Range("d" & c).Value = Me.tbDept.Value


End With

Excel program exits.

Hope someone has an answer.

Thanks

Hi,
Attached is the Workbook ("HighlightMin").... I would like to display in a message box the result including the "Cell Address and its value" on separate lines and at the same time write it to my worksheet under columns "O" and "P".

Thanks

As per the attached workbook ("HighLightMax") I would like to select the cell address for each max number in the columns of the selected range of cells.
Currently it displays the message for the max value of the cell in each column of the selected range but I am unable to select its cell address.

Hope someone can help.

Thanks

Attached is the Workbook ("Arrays")
I have a two fold problem
1) When I try to run the macro "PopulatingArrayVariable" nothing happens
2) How can I include 2 columns from "Table5" columns "A" & "B" (1 & 2) for the code:
TmpArray = myTable.DataBodyRange.Columns(1)

Thanks

As per my workbook "UniqueItems" attached I am trying to get a count of the number of cells which has the interior.color = vbyellow...but I get the count of all the cells in the selected RANGE.
Hope someone can tell me where I have got it wrong.
Thanks

Thanks

As per the attached Workbook "VB MultipleCriteria-2" I have an array formula in cell "F8"; viz.
=(SUM(SUMIFS(Qty,product,"="&F1,supplier,"="&F2:F4,delDate,">="&F5)))

Whenever, there is a change in cells "F1:F5" my vba code displays the result in cells "F6" & "F10" however, cell "F8" does not; unless i use "F9" to refresh it.

What is wrong in my formula in cell "F8"...hope someone has an answer.
Thanks

Hi Kris,
Thanks for the prompt response...however, it does not work and also when i execute the code it does not allow me to click on the worksheet...I have to "force quit" excel.
Hope you have a solution.

Thanks
PS: Attachhed is the Workbook

As per the attached Workbook "VB MultipleCriteria-Ozgrid" I am trying to use the following formula:

[f10].FormulaArray = "=SUM(SUMIFS(Qty,product,"" = "" & [f1], supplier,"" = ""&[f2:f4],delDate,"" >= ""&[f6])"

instead of

[f10].FormulaArray = "=SUM(SUMIFS(Qty,product,""=""&R[-9]C, supplier,""="" & R[-8]C:R[-6]C,delDate,"">="" & R[-5]C))"

However, I get an error message
Run-time error '1004':
Unable to set the FormulaArray property of the Range class

Also, I would like the "combo boxes" ListFillRange automatically updated when columns A,B & D is changed...the change should be reflected in Columns H,I & J alphabetically sorted (vba code for this is in "Module 1")
Thanks

Thank you very much

As per the attached Workbook "VB MultipleCriteria-Ozgrid" I am using the RC formula in the vba code for cell "F10" on my "sheet2":
[F10].FormulaArray = "=SUM(SUMIFS(Qty,product,""=""&R[-9]C, supplier,""="" & R[-8]C:R[-6]C,delDate,"">="" & R[-5]C))"

However, once the code has run I cannot then click anywhere on my worksheet...is there a solution for this?

Thanks

As per the attached Workbook "VB MultipleCriteria-Ozgrid"
When I use the excel formula in Cell "F8" =SUM(SUMIFS(Qty,product,"="&F1, supplier,"=" & F2:F4,delDate,">=" & F5)) I get correct result.
How can I do the same in Cell "F6" using "Application.WorksheetFunction.SumIfs.... based on multiple CRITERIA in COLUMN "F" in VBA

Thanks

Is there a way to use "defined names" for dynamic worksheet columns without creating a table in excel 2010 and also in VBA
Attached is the Workbook
Thanks

Re: Excel VBA code using MsgBox Join(Application.WorksheetFunction.Transpose(Range("n

Thanks NoSparks

Also, I would like to by pass the command button and have the "Column G" on the worksheets get automatically and dynamically refreshed and updated at all times.

Appreciate if you can help

Thanks

I have a command button ("Unique/Srt") on each of my worksheets ("vendor" & "Chart") that extracts unique items and sorts them alphabetically and then it displays the unique and sorted items in a "msgbox" using the code... "MsgBox Join(Application.WorksheetFunction.Transpose(Range("newchart").Value), Chr$(10))" however, I do not know how to get the "msgbox" to scroll & continue to display all the items to the end.
Also, I would like to by pass the command button and have the "Column G" on the worksheets get automatically and dynamically refreshed and updated at all times.

Hope someone can help

*Attached is the workbook.

Thanks

Re: Dynamically refresh and update Defined Names

Thank you very much Skywriter...it works just fine.

I have 2 Defined Names ("Animal" & "Names") reflecting cells in columns "B" and "F". If I have empty cells at the end and I add new data after the empty cells the Defined Names ranges does not reflect the data after the blank cells.

Is there a way to refresh and update dynamically the Defined Names in excel VBA

Attached is the worksheet.

Thanks

Re: Unique Items and Sorted

Thanks Carim for the quick response....it does work now.

I have 2 list (Names & Animals) and it extracts unique items and then sorts them alphabetically in the adjacent columns. I also have in column "A" data validation for the 2 list Cell ("A13") represents "Names" and Cell ("A16") represents "Animal"

When I enter data in Cell ("A13" or " A16") Data Validation checks if the data does not exist it will prompt if I want to ADD it to my respective list.

The problem I have is if the Data Validation is in Cell "D3" and "H3" instead of "A13" and "A16"; for the "Names" and "Animal" respectively, it does not work.

Attached is the worksheet

Hope some one can help