Re: Unable to get Points property of the series class.
Hi,
What is the value of i when you get the error (just hover the cursor over the (i) part to find this out when in debug mode).
That should help identify the problem.
Thanks,
Ian
Re: Unable to get Points property of the series class.
Hi,
What is the value of i when you get the error (just hover the cursor over the (i) part to find this out when in debug mode).
That should help identify the problem.
Thanks,
Ian
Re: Referencing User Selected Workbooks
It might be that although you've opened the workbook it is not yet active so you're previous line is not selecting a sheet wihtin the workbook you've just opened.
Try either inserting a line to activate the workbook first;
Workbooks(csvFN).Activate
csvSheetName = ActiveSheet.Name
Set rng = Workbooks(csvFN).Sheets(csvSheetName).Range("A1:C18")
or accessing the active sheet in the workbook you've just opened;
csvSheetName = Workbooks(csvFN).ActiveSheet.Name
Set rng = Workbooks(csvFN).Sheets(csvSheetName).Range("A1:C18")
Cheers,
Ian
Re: Next Available Cell Row
Corrected code below;
Sub Macro11()
Dim Wbk As Workbook
Dim WbkToCopyFrom As String
Dim Response1 As VbMsgBoxResult
Response1 = vbNo
For Each Wbk In Application.Workbooks
If InStr(1, LCase(Wbk.Name), "stat") > 0 Then
Response1 = MsgBox("Is " & Wbk.Name & " the workbook you wish to copy from?", vbYesNo, "Select Workbook")
If Response1 = vbYes Then
WbkToCopyFrom = Wbk.Name
Exit For
End If
End If
Next Wbk
WbkToCopyFrom = Wbk.Name
Range("A4:N4").Select
Selection.copy
Dim EmpRow As Range
Dim PrinttoRow As Integer
Set EmpRow = Workbooks("1.xls").Sheets("Front Sheet").Columns(1).Find("", _
After:=Workbooks("1.xls").Sheets("Front Sheet").Cells(1, 1), LookAt:=xlWhole)
PrinttoRow = EmpRow.Row
End Sub
Display More
Cheers,
Ian
Re: Fill First Blank Cell In Multiple Rows
This should be a simple way of achieving what you're after;
Option Explicit
Sub AddBrackets()
Dim i As Integer
Dim j As Integer
i = 1
Do
j = 0
Do
j = j + 1
Loop Until IsEmpty(ActiveSheet.Cells(i, j))
ActiveSheet.Cells(i, j).Value = ")"
i = i + 1
Loop Until IsEmpty(ActiveSheet.Cells(i, 1))
End Sub
Display More
Hope that helps,
Ian
Re: Report Of Employees By Date, Holidays & Sick Days
There's a slight error in the code working out the number of hours...it's using the date rather than the info on the employee row...it just so happens that the formula applied to a 2008 date = 8 hours but applied to a 2009 date = 9 hours which is where the error was coming from.
Updated section below;
x = x + 1
AbRec(0, x) = KeyMap(1, d)
AbRec(1, x) = Sheets("Data").Cells(1, b).Value
AbRec(2, x) = Sheets("Data").Cells(1, b).Value
AbRec(3, x) = Int(Right(Sheets("Data").Cells(EmpRow, b).Value, _
Len(Sheets("Data").Cells(EmpRow, b).Value) - 1))
AbRec(4, x) = d
Cheers,
Ian
Re: Next Available Cell Row
Thanks. It's a structure I use quite a lot when needing to select something from a set that isn't too big.
It saves having to write a userform with a listbox...
It can be applied to workbooks, sheets, items within a table, filtering options...anything really where you need to select one / multiple items from a set.
Re: Next Available Cell Row
I'm assuming the common part of the filename is 'stat' here;
try something like;
Dim Wbk As Workbook
Dim WbkToCopyFrom As String
Dim Response1 As VbMsgBoxResult
Response1 = vbNo
For Each Wbk In Application.Workbooks
If InStr(1, LCase(Wbk.Name), "stat") > 0 Then
Response1 = MsgBox("Is " & Wbk.Name & " the workbook you wish to copy from?", vbYesNo, "Select Workbook")
If Response1 = vbYes Then Exit For
End If
Next Wbk
WbkToCopyFrom = Wbk.Name
Display More
This will search thorugh all the open workbooks and let you select the one you want to copy from by using a messagebox to ask whichis the right one.
Then just use 'WbkToCopyFrom' as the variable everytime you need to refer to the source workbook.
Hope that's clear and helps,
Ian
Re: Next Available Cell Row
Something like;
Dim EmpRow As Range
Dim PrinttoRow As Integer
Set EmpRow = Workbooks("workbookname").Sheets("sheetname").Columns(1).Find("", _
After:=Workbooks("workbookname").Sheets("sheetname").Cells(1, 1), LookAt:=xlWhole)
PrinttoRow = EmpRow.Row
You'll need to replace sheetname and workbookname with the name of the sheet and workbook you're searching on and this assumes the workbook is open and that the first blank in column A is where you want to start adding to.
Cheers,
Ian
Re: Formula Too Big For Nesting
If you wanted a quick fix to this just create an extra column on each sheet with a formula in that gives you Year and Month in the format YYYYMM then you could easily maintain the data you need with pivot tables...
Formula would be (assuming row 2 with date in column D);
=YEAR(D2)&TEXT(MONTH(D2),"00")
Cheers,
Ian
Re: Macro To Add VLOOKUP Formula To lookup Another Workbook
I think you just need to separate out the variable in the string...
try;
'Search for comments last week from last week's file
Workbooks(this1).Activate
With Range("P2")
Range(.Cells(1, 1), .End(xlDown)).Offset(0, 1).Select
Selection.FormulaR1C1 = _
"=VLOOKUP(RC[-8],'[" & that1 & "]Raw'!C9:C18,10,0)"
Selection.Value = Selection.Value
End With
Windows(that1).Activate
ActiveWorkbook.Close
Hope that does it.
Ian
Re: Colour Textbox
Assuming your userform is called userform1, the textbox is called textbox1 and the checkbox is called checkbox1 then you'll need something like this in the code section of the userform which will fire any time the checkbox value is changed;
Private Sub CheckBox1_Change()
With UserForm1
If .CheckBox1.Value = True Then
.TextBox1.BackColor = &H80000011
Else:
.TextBox1.BackColor = &H80000005
End If
End With
End Sub
Display More
Using the Locked and Enabled properties you could also stop users from beinmg able to alter the contents of the textbox.
Hope that's useful,
Ian
Re: Delete Rows That Dont Have Certain Text
Hi Cameo,
Try this, code notated to help explain;
Sub RemoveNonMARs()
Dim WSht As Worksheet 'to scan through sheets
Dim a As Integer ' to count row we are looking at
For Each WSht In Application.Worksheets 'scan through each sheet in turn
With WSht
a = 2 'assume row 1 is title row so start on row 2
Do
If InStr(1, UCase(.Cells(a, 1).Value), "MAR") > 0 Then 'if "MAR" is in the cell somewhere
a = a + 1 'move to next row
Else:
.Rows(a).Delete 'otherwise delete the row
End If
Loop Until IsEmpty(.Cells(a, 1)) 'keep going until you cvome to an empty row
End With
Next WSht 'move to next worksheet
End Sub
Display More
Cheers,
Ian
Re: Conditional Formatting For N/a Fields
I would go for using the iserror formula...
=IF(ISBLANK(A7),"",IF(ISERROR(VLOOKUP(A7,Sheet1!$A$1:$A$828,1,False)),"Missing Account","Match Found"))
Cheers,
Ian
ADDED BY ADMIN
=VLOOKUP(A7,Sheet1!$A$1:$A$828,1,False)
In 1 column (eg "B") and;
=IF(ISNA(B1),"Missing Account","Match Found")
Hide column B
Re: Report Of Employees By Date, Holidays & Sick Days
Hi MJB123,
I've quickly knocked up something that should get you somewhere near where you need to be...
The sheet (attached) now has a userform to select the employee to run the report for, a command button on the data sheet to start up the macro, a report template page to copy the format for each individual and a key sheet which relies on the following;
All codes for possible absence types listed.
All codes are only one letter long.
The order of the codes is the same as they appear in the columns on the report.
here's the code;
Sub EmployeeReport_Click()
Dim a As Integer
Dim b As Integer
Dim c As Integer
Dim d As Integer
Dim x As Integer
Dim y As Integer
Dim EmpRow As Integer
Dim KeyMap() As String
ReDim KeyMap(1, 99)
Dim AbRec() As Variant
ReDim AbRec(4, 365)
Dim NewSht As Worksheet
c = 1
Do
KeyMap(0, c) = Sheets("Key").Cells(c, 1).Value
KeyMap(1, c) = Sheets("Key").Cells(c, 2).Value
c = c + 1
Loop Until IsEmpty(Sheets("Key").Cells(c, 1))
ReDim Preserve KeyMap(1, c - 1)
Load EmpSelect
a = 2
With EmpSelect.EmpCmb
.Clear
Do
.AddItem Sheets("Data").Cells(a, 1).Value
a = a + 1
Loop Until IsEmpty(Sheets("Data").Cells(a, 1))
End With
EmpSelect.Show
EmpRow = EmpSelect.EmpCmb.ListIndex + 2
b = 2
Do
If Sheets("Data").Cells(EmpRow, b).Value <> "" Then
For d = 1 To UBound(KeyMap, 2)
If KeyMap(0, d) = Left(Sheets("Data").Cells(EmpRow, b).Value, 1) Then Exit For
Next d
x = x + 1
AbRec(0, x) = KeyMap(1, d)
AbRec(1, x) = Sheets("Data").Cells(1, b).Value
AbRec(2, x) = Sheets("Data").Cells(1, b).Value
AbRec(3, x) = Int(Right(Sheets("Data").Cells(1, b).Value, _
Len(Sheets("Data").Cells(EmpRow, b).Value) - 1))
AbRec(4, x) = d
End If
b = b + 1
Loop Until IsEmpty(Sheets("Data").Cells(1, b))
Set NewSht = ActiveWorkbook.Sheets.Add
With NewSht
Sheets("Report Template").Cells.Copy Destination:=.Cells
.Cells(3, 2).Value = EmpSelect.EmpCmb.Value
For y = 1 To x
.Cells(5 + y, 2).Value = AbRec(0, y)
.Cells(5 + y, 3).Value = AbRec(1, y)
.Cells(5 + y, 4).Value = AbRec(2, y)
.Cells(5 + y, 5 + AbRec(4, y)).Value = AbRec(3, y)
Next y
.Rows(5 + y & ":370").Delete
.Name = "Absence - " & .Cells(3, 2).Value
z = 6
Do
If ((.Cells(z, 2).Value = .Cells(z + 1, 2).Value) And _
(.Cells(z, 4).Value + 1 = .Cells(z + 1, 3).Value)) Then
.Cells(z, 4).Value = .Cells(z + 1, 4).Value
For e = 6 To UBound(KeyMap, 2) + 5
If .Cells(z, e) <> "" Then .Cells(z, e).Value = .Cells(z, e).Value + .Cells(z + 1, e).Value
Next e
.Rows(z + 1).Delete
Else:
z = z + 1
End If
Loop Until IsEmpty(.Cells(z, 2).Value)
End With
Application.Calculate
Unload EmpSelect
End Sub
Display More
and on the userform
Private Sub OKBtn_Click()
If EmpSelect.EmpCmb.ListIndex <> -1 Then
EmpSelect.Hide
Else:
MsgBox ("Please select an employee from the list!")
End If
End Sub
Display More
First it summarises each nonn-blank column for the employee into a data record, then it puts that on a fresh template.
Finally it then scans thorugh what it has just done and combines any records on consecutive days of the same absence type.
Hope that Helps!
Feel free to let me know any issues
Cheers,
Ian
Re: Oleobject, Trigger Event, Not Firing
Sorry I obviously read your post a bit quickly last time and tried to come up with too simple a solution.
I think the key here is to not delte the buttons at all. I would just create an array that after stripping out the numbers, instead of deleting them, just finds the first number not already taken and uses that as the name for the new command button...
That way you don't get any name clashes and you don't have to delete any which is what is probably causing the link to the macro to break down.
Something like;
Sub A_A_Utility_ButtonCreationForHOME_PopulateViewEditItemButton()
Dim M As Long
Dim I As Long
Dim Name As String
Dim NName As String
Dim NumItems As Long
Dim NumUsed(999) As Boolean
Dim x As Integer
Dim y As Integer
For x = 1 To UBound(NumUsed)
NumUsed(x) = False
Next x
Range("A3").Value = "=COUNTA(A4:A1000)" 'Count...
NumItems = Range("A3").Value
Range("A3").Value = ""
'=============Delete Buttons=================
For Each OLEObject In Sheets("HOME").OLEObjects
If Left(OLEObject.Name, 10) = "ItemButton" Then
Name = Right(OLEObject.Name, Len(OLEObject.Name) - 10) ' Take the letter to the right of those 10 letters as call them the variable (string) "Name"
NumUsed(CInt(Name)) = True
End If
Next OLEObject
'=============Then Add the Buttons Back========= (prevents double creation of buttons)
Range("A" & 2 * I + 3).Select
Dim ItemButtonObj As OLEObject, N%
Set ItemButtonObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, Left:=ActiveCell.Left + 2, Top:=ActiveCell.Top - 4, _
Width:=1.93 * ActiveCell.Width, Height:=18)
For y = 1 To 999
If NumUsed(y) = False Then Exit For
Next y
ItemButtonObj.Name = "ItemButton" & y
ItemButtonObj.Object.Caption = "View/Edit Entries"
ItemButtonObj.Object.Font.Size = 8
End Sub
Display More
That's not tested so it may not work 100% but it should get you in the right direction.
Cheers,
Ian
Re: Create Chart With Dynamic Named Ranges
I think the problem is that you are Dim'ing rng and val as ranges, then declaring them as strings...
As .XValues and .Values as members of the .Series object are variants they can be either a string or a range, but not both.
Make sense?
Ian
Re: Change Caption Of Label In Form
Hi ANdy,
Maybe you need to send an example because I don't think I'm getting why this is a problem...
You can change the caption of a label at anytime as long as you have loaded the userform the label sits on (you don't even need to hide the form to do so).
But if you want it to change just use the same line of code, just before you show the form for the second time, e.g.
Sub ChangeLabelTwice()
Load UserForm1
With UserForm1
.Label1.Caption = "title you want first"
.Show 'show for the first time
'do some other stuff here as code will restart here when form is exited
.Label1.Caption = "title you want second"
.Show ' show form with calendar on for second time, with new title
Unload UserForm1
End Sub
Display More
Hope that's better.
Ian
Re: Oleobject, Trigger Event, Not Firing
Hi There,
I've encountered similar problems before.
It's normally that the EnableEvents property isn't set to TRUE. Although this is the default for Excel the property can sometimes be upset by breaking your code halfway through or when your code hits an error.
My way around it when I'm writing code is always to have a command button that fires;
Which I click before testing anything with triggers, and then I'm finished you can add the same line of code as the first line of code against all your command buttons in the workbook.
That way you can guarantee, as long as the user has clicked a command button first, that the events should fire every time.
I know that's not a specific solution to your problem but I hope it gets you in the right direction.
Hope that helps,
Ian
Re: Change Caption Of Label In Form
Hi Andy,
This should be pretty easy...
If you want to change the caption on a userform that the calendar control is inserted on (say the userform is called UserForm1) then use;
The other way would be to insert a label in the userform and have that changing value (if Label was called Label1);
Or you could have a msgbox pop up before the form to say which date they should enter...there's lots of ways...
Hope that helps. Let me know if you need something more specific.
Ian