Re: Use VBA Code to Apply Conditional Formatting to Multiple Cells w/ 5 conditions ea
Hi Mike:
I used the sum function because your initial post was - to highlight if the cells from Q9 to U9 are zero. So you can avoid using "AND/OR" arguments.
If I understand you correctly, you can replace SUM with "Count" function to get the expected result.
Regards
Maqbool
Re: Use VBA Code to Apply Conditional Formatting to Multiple Cells w/ 5 conditions ea
Hi Mike:
Sorry. It was not tested and not realized and array wont work in conditional formatting. The attached workbook has a solution to your problem.
Regards
Maqbool
Re: Use VBA Code to Apply Conditional Formatting to Multiple Cells w/ 5 conditions ea
Hi Mike:
Use the following formula in conditional formatting. Hope this will help.
[F]
AND((OR(B9={"M1151A1","M1165A1","M1079","M1083","M1073"})),SUM($Q$9:$U$9)=0)
[/F]
Regards
Maqbool
Re: Sumif based on criteria
Hi
Try the following "array formula".
[F]
=IFERROR(SUM(IF(COUNTIF($A$2:A2,A2)=COUNTIF($A$2:$A$7,$A2),IF($A$2:$A2=$A2,$B$2:$C2),"")),0)
[/F]
Regards
Maqbool
Re: From each value in "sheet2", Find in "sheet2" and print reference details in "she
Hi
Try the following.
Sub GetData()
Dim wsOne As Worksheet
Dim wsTwo As Worksheet
Dim rngIDs As Range
Dim rngID2 As Range
Dim lstRw As Long
Dim curid As Range
Dim c As Range
Set wsOne = Sheet1
Set wsTwo = Sheet2
Application.ScreenUpdating = false
With wsOne
lstRw = .Rows.Count
Set rngIDs = .Range("A2", .Range("A" & lstRw).End(xlUp))
End With
With wsTwo
Set rngID2 = .Range("A2", .Range("A" & lstRw).End(xlUp))
End With
With rngIDs
For Each c In rngID2
Set curid = .Find(c, , xlValues, xlWhole, , , True)
If Not curid Is Nothing Then
Range(curid.Offset(0, 1), curid.Offset(0, 3)).Copy
c.Offset(0, 1).PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
Else
c.Interior.ColorIndex = 5 'change as needed
End If
Next
End With
Application.ScreenUpdating = True
End SubRegards
Maqbool
Re: Dynamic Dates
Hi
Welcome to the forum.
Try the attached.
Regards
makbool
Re: Advanced filter unique value macro with button
Hi Help99
Welcome to the forum.
Try the attached.
Sub AdvFltr()
Dim wsData As Worksheet
Dim rngFltr As Range
Dim i As Long
Set wsData = ThisWorkbook.Sheets("Sheet1")
With wsData
i = .Rows.Count
Set rngFltr = .Range("A1", .Range("A" & i).End(xlUp).End(xlToRight))
.Range("H1").CurrentRegion.Clear
End With
With rngFltr
.AdvancedFilter xlFilterCopy, , wsData.Range("H1"), True
End With
End SubModify according to your needs.
Regards
Makbool
Re: Hiding selected zeroes in a worksheet
Hi
Welcome to the forum.
Please have a look at the following link:
http://office.microsoft.com/en-001/excel-help/display-or-hide-zero-values-HP010070461.aspx
Maqbool
Re: Position Chart in Range
Hi VMF:
Sorry. It is my mistake and apologies. I have asked you to remove the line to add the chart in the sheet. sorry for that. The following line on your code must be there to create the chart.
Sheets(shname).Shapes.AddChart.Selectand I have added one more line in my code to skip the positioning of charts if there is no chart in the sheet
Public Sub PositionChart()
Dim ws As Worksheet
Dim curChart As ChartObject
Dim intTop As Long
For Each ws In ThisWorkbook.Sheets
With ws
intTop = .Range("C9").Top
intleft = .Range("C9").Top
intwdth = .Range("C9:M9").Width
intHight = .Range("C9:M29").Height
On Error Resume Next ' to skip the chart positioning if there are no charts in the sheet
Set curChart = .ChartObjects(1)
With curChart
.Top = intTop
.Left = intleft
.Height = intHight
.Width = intwdth
End With
End With
Next
End SubHope everything will work as you expected.
Regards
Maqbool
Re: Position Chart in Range
Hi
Remove the following from your code.
Sheets(shname).Shapes.AddChart.Select
With ActiveChart.Parent
.Top = Range("C9").Top
.Left = Range("C9").Left
.Width = Range("C9:M9").Width
.Height = Range("C9:M28").Height
End Withthen create a new procedure as below to align the charts on each sheet
Sub PositionChart()
Dim ws As Worksheet
Dim curChart As ChartObject
Dim intTop As Long
For Each ws In ThisWorkbook.Sheets
With ws
intTop = .Range("C9").Top
intleft = .Range("C9").Top
intwdth = .Range("C9:M9").Width
intHight = .Range("C9:M29").Height
Set curChart = .ChartObjects(1)
With curChart
.Top = intTop
.Left = intleft
.Height = intHight
.Width = intwdth
End With
End With
Next
End Suband finally call the above at the end of your chart creation code by adding the following line at the end of your code.
PositionChartRegards
Maqbool
Re: Sumifs
Hi Atomic_Sheep
by a little tweak of NBVC's formula, you can make it.
1. create a named range (rngExpense) for the expense list (table 2 in your example)
2. Use A1 to select your years (Datavalidation List). See the attachement
3. In B1 enter the following formula:
[f]=IFERROR(DATE($A$1,COLUMN()-1,1),"Select Year")[/f]
4. in B2 enter the following formula:
[f]=SUMIFS(INDEX(rngExpense,0,MATCH($A2,Sheet2!$1:$1,0)),Sheet2!$A$1:$A$33,">="&B$1,Sheet2!$A$1:$A$33,"<="&EOMONTH(B$1,0))[/f]
Regards
Maqbool
Re: Search value from data sheet, copy the row to another sheet and then strikethroug
Hi
Change your code as below:
For Each rngCel In rngSource
If rngCel.Value = refdlt Then
strReason = "Reason" ' change to your textbox value on form
With wsDestin
'Following line assumes column headers in Destination worksheet
lngDestinRow = .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0).Row
rngCel.EntireRow.Copy Destination:=wsDestin.Cells(lngDestinRow, "A")
Application.CutCopyMode = False
Selection.End(xlToRight).Offset(0, 1).Value = strReason
End With
End If
Next rngCel
valFound.EntireRow.Font.Strikethrough = True
End IfRegards
Maqbool
Re: Search value from data sheet, copy the row to another sheet and then strikethroug
Hi
Change your code as below:
Sub strikeIt()
Dim ws As Worksheet
Dim rng As Range
Dim valFound As Range
Set ws = ActiveSheet
Set rng = ws.Range("A1:A100") 'change accordingly.
With ws
Set valFound = rng.Find("aa", , xlValues, xlWhole) 'use value from the text box instead of "aa"
If Not valFound Is Nothing Then
valFound.EntireRow.Font.Strikethrough = True
'copy paste code here
End If
End With
End SubRegards
Maqbool
Re: count number of times cells value is greater than 105 and are in consecutive
Hi
See the attachment will help you .
Regards
Maqbool
Re: Summary table of stock returns in VBA - is it doable?
Hi
You can remove subtotal of each field that you don't want by going the field settings.
See the attachment.
Regards
Maqbool
Re: Summary table of stock returns in VBA - is it doable?
Hi Sameerbuch:
It can be done with a Pivot Table. Dont know why you didn't prefer a PT. See the attached.
Bloomberg has a function to replace the errors. (I am not in-front of pc with bloomberg). Either you can use it or use if function.
Regards
Maqbool
Re: Use MS Query from a sheet in the same workbook
Hi
It seems that Excel Files are not added in your User DSN. Goto ODBC Data Source Administrator and see Excel is there.
Regards
Maqbool
Re: Dlookup Function in Access 2007
Hi
This depends on your Month-Year field on your table 2. Following link has detailed explanation about dlookup.
Re: Multiple Users input data using Excel Userform to a Common Access Master Database
Hi
The following link will help you to do the same;
http://support.microsoft.com/kb/246335
Regards
Makbool
Re: VBA Pie chart data labels in percentage, but need to exclude 0 (zero's)
Hi
Here is my suggestion.
Instead of removing the 0%, use #N/A on that data point. This will not plot on the chart.
See the attached chart.