Re: Networkdays Function
Rob,
This should do it.
NB: dates are inclusive, change function name to something less wordy that will be relevant to you.
Regards
Weasel
Function NetWorkDaysWithSats(Start As Long, Finish As Long)
Dim i As Integer
Dim x As Integer
Dim z As Integer
i = Finish - Start
For x = 0 To i
If Weekday(Start + x) > 1 Then
z = z + 1
End If
Next x
NetWorkDaysWithSats = z
End FunctionRe: SUbtraction Rounding Error
Heres a bit more on the problem:
http://support.microsoft.com/kb/214118/EN-US/
Regards
Weasel
Re: vba conditional action
Difficult to tell with only some of code and no real idea of the desired end result. See below - you will need to edit the End If statements depending on the desired result. As the offset enumeration is dependant on the range given by rngimport it should find the cell 8 spaces to the right of the cell referred to by this variable each time.
Post back if this doesnt have the desired effect.
Regards
Weasel
objwsk1.Select 'import sheet creation
Set rngfoundcells = objwsk1.Range("a1")
If rngfoundcells = 0 Then
MsgBox "Important Message" & vbCrLf & _
"Valuation updates complete no import sheet produced" & vbCrLf & _
"as there are no cash withdrawals", vbExclamation ' msgbox to indicate no import to be done
Worksheets("Import data").Delete
Worksheets("Import ADN").Delete
Workbooks("PEP dBase").Close savechanges:=False ' Closure of PEPdBase
Exit Sub
Else
With objwsk1 'this takes the data from worksheet import data and creates a import sheet in the correct format
Set rngimports = .Range("a1:a" & Range("a65536").End(xlUp).Row)
For Each rngimport In rngimports
Set rngnewman = rngimport.Offset(0, 7)
Set rngtfrtyp = rngimport.Offset(0, 6)
Set rngcheque = rngimport.Offset(0, 8)
introw = introw + 1
If rngcheque = 0 then
objwsk2.Range("a" & introw) = "ADNPCPT" & strrefdate & Format(Now(), "hhmm")
else
objwsk2.Range("a" & introw) = rngcheque
'place End If here to have following formatting code execute regadless of outcome of If statement
objwsk2.Range("c" & introw) = Format(Date, "d/m/yy")
objwsk2.Range("d" & introw) = Format(Date, "m")
objwsk2.Range("e" & introw) = "C"
objwsk2.Range("h" & introw) = rngtfrtyp & " " & rngnewman & " " & strmydate
objwsk2.Range("i" & introw) = "D"
objwsk2.Range("j" & introw) = (rngimport.Offset(0, 0) * 100) + (rngimport.Offset(0, 2))
objwsk2.Range("k" & introw) = rngimport.Offset(0, 3)
objwsk2.Range("n" & introw) = rngtfrtyp & " " & rngnewman & " " & strmydate
objwsk2.Range("o" & introw) = "N"
'place End With here to have code formatting code execute only after a result where rngcheque is not 0.
Next rngimport
ActiveWorkbook.Save
End IfRe: vba conditional action
adncmm,
How about:
if rngcheque = 0 then
'yourcode
Else
'your other code
End IfNB: use 0 to detect a cell that contains a zero value, use "" to detect an empty cell
Regards
Weasel
Re: DSUM and Pivot Chart
Mike,
I havent looked at your data but have a look at excels help files on the GETPIVOTDATA function. This may accomplish what you require.
Regards
Weasel
Re: Disappearing paste>?
While not being overly sure what the end result should be, the paste section is still working fine, assuming that is that the delete procedure doesnt end up deleing the data in E3:Y4.
Regards
Weasel
Re: Disappearing paste>?
Works fine for me. Are there any other procedures run after this - in particular I would be looking for a sheet level procedure being run on the sheet change event for the target sheet?
Regards
Weasel
Re: Disappearing paste>?
Hard to say without seeing the code......
Re: find vendor name on customer list
acctlab,
A little hard to give a firm answer without seeing a better snapshot of your data, but in short - it is hard to match data if the two data sources dont match. What your are asking is for excel to make a guess as to whether they refer to the same company name or not.
For a text comparison:
'abc company, inc' = 'abc co.'
is just as false as
'abc company, inc' = 'special fried rice'
Regards
Weasel
Re: Is there a way to show more tabs?
Wrecking Crew,
AFAIK there is no way to have a split level sheet tabs bar (the same effect as increasing the height of the Windows Task Bar). You could build a custom control to place in the right click menu for the sheet or in a custom toolbar but this would be a little redundant as the option is available by right clicking on the |< < > >| arrows to the immediate left of the sheet tabs.
Regards
Weasel
Re: Show UserForm upon Opening Workbook
mileseve,
Not sure what you mean by inactive. By default you are unable to use a worksheet while a userform is displayed - unless you are using a modeless userform.
If you want your worksheets hidden while the userform is displayed (remembering that at least one must be displayed - this would typically be the splash screen) try using worksheet visible in the inialize/terminate events of the userform.
eg. in the code section of the userform:
Private Sub UserForm_Initialize()
Sheets("sheet1").Visible = 0
Sheets("sheet2").Visible = 0
End Sub
Private Sub UserForm_Terminate()
Sheets("sheet1").Visible = 1
Sheets("sheet2").Visible = 1
End SubJust be sure to use
Unload userform1
NOT
userform1.hideto make the form go away.
Regards
Weasel
Re: Detect new datas
No need to select the range.
just goto insert - Name - Define.
Type the name of the range in the 'Names in Workbook' box and type the formula in the 'Refers to Box'
Regards
Weasel
Re: Detect new datas
Match is the function you need for the job:
=IF(ISNA(MATCH(A2,parts,0))=TRUE,"New",1)
for this example I created a Dynamic Range called parts that will expand as you add parts to the list on sheet1. see attached.
Regards
Weasel
Re: range vs dynamic range
booger,
Thats the nuts & bolts of it, yes.
eg.
Named Range
myRange = A1:B5
will refer only to that range.
Dynamic Range
myRange = Offset($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))
will start at A1 and will go down as many rows as have data in in column A and across as many columns as have data in Row 1. As you add contiguous columns and rows the range expands to include them.
Regards
Weasel
Re: ListBox 2 column additem
Dave,
Yes that should add A & B to the list box 10 times.
Regards
Weasel
Re: ListBox 2 column additem
Dave,
In this example I have a list box with 5 columns. The values for each column are filled by a1:e1
ListBox1.AddItem range("a1").value
ListBox1.List(1, 1) = range("b1").value
ListBox1.List(1, 2) = range("c1").value
ListBox1.List(1, 3) = range("d1").value
ListBox1.List(1, 4) = range("e1").valueHope that helps
Weasel
to make this truly useful you should be looping through data to obtain a dataset of entries. You need to post more info to get more help with that.
Re: database transfer
modi,
If it is a 3rd party app doing the exporting then you are probably in a little trouble.
A copy of the excel report you have posted to the board would help, it may be possible to run code on the reports to sort them out..
Regards
Weasel
I am off to bed now - try to get back to you tomorrow.
Re: CopyFromRecordset
inung,
How are you populating the recordset?
Regards
Weasel
Re: Button to increment cell number
The internet is full of great websites that provide help with VBA - but dont go to far - you're already at the best.
Check out some of the tips and tricks on the mothersite - here as well as posts on the board.
Also have a look in the bookshop, there are some great learner resources there.
Another good way to pick up syntax is record macros and examine the (inefficient but accurate) code generated, then play with it till it does what you want. This isn't possible 100% of the time but is great way to learn.
Regards
Weasel
Re: Show UserForm upon Opening Workbook
mileseve1,
As Dave has said - hiding the workbook would be a bad way to go.
However there are plenty of options around using Splash Screens - where only certain sheets are hidden, not the entire application. Do a search on board for Splash Screens and see what comes up.
Regards
Weasel