Posts by ProjectFocus

Re: Tax Year Date Splitting

I have managed to do it now. I formatted all of the variables as date and did a <=>= for the tax cut off points with the year being entered by the user. All of the validation for the dates now works.

Thanks for your help anyway.

Re: Tax Year Date Splitting

Another try

Re: Tax Year Date Splitting

Here you go. If you use the run report it will need to be closed and not saved as it adds the data from a template. The version I am adding is not a template. This means it can only be run once.
The data on the stats page will be updated with company data before the reports are run.

I cant upload it as it is exceeds to limit. I will see if I can make it smaller. it is 49kb.

Re: Tax Year Date Splitting

The reason it is needing to be VB is that the pages are generated and the data is generated from VB. I will attach the excel template and see if you can see what I mean.

This is purely for stats and working out sales staff and how they are doing in each month by tax year.

Let me just change the data slightly as I cant send it to you with all our company data on it. One sec.

Re: Tax Year Date Splitting

At the moment I am saving the Date field as s tring and splitting the dates and atributing this to a month name and adding counts depending on the month.

I know this is not the best way to code it but it was written a while ago and I don't want to have to reinvent the wheel. I can change it to date format but I am not sure about how I could split it by tax year using it in date format.

Re: Tax Year Date Splitting

What I am looking for is a way of getting a date that is in a long list of data. I then want to split it into a tax year specified by the user. If the date in the list is in the selected tax year to use this date and split it by month. Then add a count to a column that is under the month to show how the spread of dates lie within the tax year.

There will be dates that don't fall within the specified tax year and I am not interested in them. I have a sheet that has the code for splitting by month into the format of tax year but it still reads all data not just the tax year data. This is a sheet that is running quite abit of code and does otherfunctions that is the only reason I have not attached it.

Re: Tax Year Date Splitting

Well It would be April 1st 06 to March 31st 07 inclusive.
It would be better to use it from the 5th of April but I am sure that would be harder coding.

Re: Macro for copying certain cells to another sheet?

Try

Private Sub Kopiering_Click()


    worksheets("TOTAL").Range("K3").value = worksheets("BRL Newbuilding").Range("E3").value
    
    worksheets("TOTAL").Range("J3").value = worksheets("BRL Newbuilding").Range("D3").value


    worksheets("TOTAL").Range("E3").value = worksheets("BRL Newbuilding").Range("F3").value


    worksheets("TOTAL").Range("F3").value = worksheets("BRL Newbuilding").Range("G3").value


    worksheets("TOTAL").Range("G3").value = worksheets("BRL Newbuilding").Range("H3").value


    worksheets("TOTAL").Range("I3").value = worksheets("BRL Newbuilding").Range("J3").value


    worksheets("TOTAL").Range("L3").value = worksheets("BRL Newbuilding").Range("C3").value


    worksheets("TOTAL").Range("M3").value = worksheets("BRL Newbuilding").Range("K3").value


End Sub

I have a issue. I have a date that is save as a date variable. Is there any quick functions that can be used in VBA that will get a date split by tax year.

For Example

If I was wanting to look at tax year 06 this would be from April 06 to March 07. The year would have to be determined by a entered year Input box.
After it has been split by year I then if it falls between the dates to split the date by Month. Is there any simple functions and comparitors I can use on a date defined variable.

Thanks for your Help.

Re: Date Ranges

Sorry I was being silly and it is purely a <= and >= to check on the dates.

I am wanting to use a date range function and not sure how to go about it.
I want to have 3 variables. DateStart, DateEnd, DateSearch.

Basically I want to check if the date Search is inbetween the 2 other dates. I am not sure how I would do this. I gather there is something that will work if the variables are set as type Date. I am not sure of the functions availiable with this variable type. The Start and end date will be entered from a input box and the Dateserach will be from a list of dates.
Basically I want to add data from the row that has a date that falls between the dates.

Your help is much Appreciated.

Re: Delimiting Fields

When I tried a find and replace it did not find any on 0127.

I did make another program to find out the ascii char that would split the fields. It went through all of the chars in turn and trying to split the field. On the row that split the field correctly I knew the ascii number that represented the .

This happens to be 13. So I made sure that in the input box if shomeone enters "0" in the box it changes the code the Ascii 13. This then split the fileds correctly.

So just so you know the ascii value for is 13. Well atleast in this case it was.

Thanks for all your help.

Re: Delimiting Fields

I was thinking that as well so I tried to enter the delimiting field as the ascii command for LF CR. This did not work either. It is a pain because I cant even use a find and replace function.

Is there anything anyone could think of getting round this issue.

Re: Delimiting Fields

Ok here is a update. I have found the ascii code for the square and that is 127. I have used this to try and delimit by that ascii char and although it brings up the right symbol if I show it in a message box it will not delimit to it. Any Ideas

I have another issue for you.

I have a field that needs to be split into a set amount of columns depending on a delimited character. This works fine with all of your standard delimeted characters such as / or ,. The only issue is I have a field that has a delimiter of the square symbol □. How would I be able to insert this symbol into a input box.
When I try and copy the symbol from excel it does not show in the inputbox. I also tried to copy it from the sybol that can be generated in word and that just pastes as ?.

I was wanting to know if there is anyway this can be entered into a variable so that I can delimit the data using this symbol. If I have to use a ascii value I don't mind I just cant find a ascii code for it.

Re: Using Split function

Quote from Andy Pope

As you know, or can at least determine, the limits why not use a For Next loop?
[vba]For ii = LBound(SplitContent) to UBound(SplitContent)[/vba]

Yeah I suppose a for next loop would be a better way of doing it. Just got in the habit of using do until loops.

Re: Using Split function

Quote from carlmack

maybe just change to

Code

Loop Until ii = UBound(SplitContent) + 1

This worked like a charm.

Re: Using Split function

It works but it does not run the loop to print the data on the last run through. i.e the data does not get added to cells when the amount of cells set by the user gets to the end.

Not explaining well.
say amount of columns was set to 4 and it adds 4 columns. It will print the data onto all of the columns except the 4th on.

I have been using the split function to split up some delimited fields.

The only issue is that when I try to split them they will not all contain the same amount of splits. I want to be able to have some code that will not bring back a subscript out of range if I try and call a split that does no exist.

Here is the code I am using but it will always stopwhen trying to call a field that does not contain the amount of splits that were declared.

Sub SplitData()




Dim ColumnSplit As String
Dim AmountOfColumnsAdd As Integer
Dim ColumnAddRange As String
Dim InputContent As String
Dim SplitCondition As String
Dim SplitContent As Variant
Dim i As Long
Dim ii As Long


Dim AsciiColumn As Integer
Dim NextColumn As String
Dim FieldContent As String




ColumnSplit = InputBox("Please Enter the column that holds the data to Split")
AmountOfColumnsAdd = InputBox("Enter the amount of columns to split the data into")
SplitCondition = InputBox("Please enter the Character the is used to split the data")


AsciiColumn = Asc(ColumnSplit)
AsciiColumn = AsciiColumn + 1


NextColumn = Chr(AsciiColumn)


i = 0
Do
    Columns(NextColumn).Insert Shift:=xlToLeft
i = i + 1
Loop Until i = AmountOfColumnsAdd




' Search all of the records and split into the new columns.
i = 2
Do
    FieldContent = Worksheets("Sheet1").Range(ColumnSplit & i).Value
    If FieldContent <> "" Then
        SplitContent = Split(FieldContent, SplitCondition)
        
        ii = 0
        Do
        
            If SplitContent(ii) <> Null Then
                Worksheet.Range(NextColumn & i) = SplitContent(0)
            End If
        
            Worksheets("Sheet1").Range(NextColumn & i).Value = SplitContent(ii)
            AsciiColumn = Asc(NextColumn)
            AsciiColumn = AsciiColumn + 1


            NextColumn = Chr(AsciiColumn)
                   
            ii = ii + 1
    
        Loop Until ii = AmountOfColumnsAdd
    End If
    
i = i + 1
AsciiColumn = Asc(ColumnSplit)
AsciiColumn = AsciiColumn + 1
NextColumn = Chr(AsciiColumn)
Loop Until i = 65000


MsgBox ("Finished")




End Sub

Re: Adding to string Values

That words brilliantly. Thanks for that.