Posts by megatronixs

Hi all,

I have this code here that I use to filter on a particular date. I will later on do this with a variable. in the column, there is also time in the cell "11/09/2017 09:33:39"
How can I have this sorted?

Worksheets("raw_data").Range("$A$1:$AW$56000").AutoFilter Field:=10, Operator:=xlFilterValues, Criteria2:=Array(2, "9/11/2017")

Greetings.

Hi all,

I have a range in excel sheet where there are 5 possible choices to make. they all have a value in percentage. If the user will put an X in "never" it will have value of 0%, if they user puts X in "sometimes", it has a value of 6.25%
There are 5 questions where the user can put a X depending on the answer she/he will give.
these are the values I have added:
Never = 0%
Sometimes = 6.25%
Often = 12.5%
Mostly = 18.75%
Always = 25%

I a user would have chosen like this:
(range N3) question 1= Never
(range O3) question 2 = sometimes
(range P3) question 3 =sometimes
(range Q3) question 4 =often
(range R3) question 5 =mostly

I should get the total in the values 43.75%

Any idea how to solve this would be really great.

Greetings.

Re: with SQL resutls containing a certain date, write yes or no

Hi all,

No possible solution?

Greetings.

Hi all,

I have a SQL query that I run to get data from a database (Teradata). The criteria is a company ID number. I need to check if they have closed some products with us. There is column called "closed_date" where the date is placed. If the product is not closed, then the date is set as 30/06/2099.
They could have various products and I need to figure out if they have at least one open.
I run it from a excel userform where I have a listbox with where I put the id and can run a batch for a lot of them.
I was wondering how I could change the code and query to give the results like below:

Customer ID: Closed:
1234567890 yes
2739392024 no
7392873830 no

Please see the current code I have:

Private Sub CommandButton11_Click()
    Dim LastRow As Long
    Dim conn As ADODB.Connection
    Dim rec1 As ADODB.Recordset
    Dim thisSql As String
    Dim uiValue As String
    Dim login As String
  
    login = UserForm3.login.Text
    uiValue = UserForm3.uiValue.Text
    
    Dim strText() As String
    Dim i As Long
Dim StartTime As Double
Dim SecondsElapsed As Double
'Remember time when macro starts
  StartTime = Timer


LastRow = Sheets("Results").Cells(60000, 1).End(xlUp).Row + 2
    strText = Split(CinBin.Text, Chr(10))
    For i = 0 To UBound(strText)
        'Do something with strText(i)
      Set conn = New ADODB.Connection
conn.CommandTimeout = 900
conn.Open "DSN=CLPROD;Databasename=DB_Cust_Prod;Uid=" & login & ";Pwd=" & uiValue & ";"
'   run this SQL to get the data
thisSql = "SELECT cust_id AS ""Customer ID"", closed_date AS ""Closed Date"" " & _
"FROM DB_Cust_Prod.Products WHERE (closed_date= '2099.06.30') AND ((cust_id)= " & strText(i) & ")"
LastRow = Sheets("Results").Cells(60000, 1).End(xlUp).Row + 3


Sheets("Results").Cells(LastRow, 1).Value = "Customer ID"
Sheets("Results").Cells(LastRow, 1).Interior.Color = vbYellow
Sheets("Results").Cells(LastRow, 2).Value = "Closed Date"
Sheets("Results").Cells(LastRow, 2).Interior.Color = vbYellow
LastRow = Sheets("Results").Cells(60000, 1).End(xlUp).Row + 1
Set rec1 = New ADODB.Recordset
rec1.Open thisSql, conn
Sheets("Results").Cells(LastRow, 1).CopyFromRecordset rec1
    Next i


Cells.EntireColumn.AutoFit
    conn.Close
    Set conn = Nothing
    
  MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
Me.TimePased = MinutesElapsed
End Sub

Greetings.

Re: copy all excel files in all folders and subfolders based on excel sheet with file

Hi SO,

we managed to make it work
Thanks for the link.

Greetings.

Re: copy all excel files in all folders and subfolders based on excel sheet with file

Hi all,

No possible solution for this?

Greetings.

Hi all,I made only the below code work for only the excel files I have in a folder, but it does not copy the excel files from the subfolders.I have a list with excel files that need to be copied to another folder. There are more files there, but I need only the list that is in a range.any help on this?

Sub mojkod() 
    Dim R As Range
    Dim FromPath As String
    Dim ToPath As String
    Dim FName As String 


'Setup source and dest path (Note: must have a trailing backslash!)  FromPath = "Y:\test\z\"  ToPath = "Y:\test\do\"


'Visit each used cell in column A
    For Each R In Range("A1", Range("A" & Rows.Count).End(xlUp))
        If Not FSO.FileExists(FromPath & FName) Then
            MsgBox "File Not Found", vbInformation, "Not Found"
        End If


'Search the file with the file mask from the cell (Note: can contain wildcards like *.xls)    FName = Dir(FromPath & R)    'Loop while files found
        Do While FName  ""
'Copy the file
             FileCopy FromPath & FName, ToPath & FName
'Search the next file
            FName = Dir()
        Loop
    Next


End Sub

Hi all,

I have the below code to change the format of a date to a format that the SQL will understand.

' change the format of the dates into a format of a date to a format that the SQL will understand'With Sheet3.Range("A2", "B100")
'    .Value = .Worksheet.Evaluate("INDEX(IF(" & .Address & "<>"""",TEXT(" & .Address & ",""'yyyy.mm.dd""),""""),)")
'End With

what I would need now is to change the format back to what it was before at the end when the whole code did run.

first it will covert "24/11/2016" to "20161124"
and now I need to convert it from "20161124" to "24/11/2016"

I tried to do this, but it fails as I don't understand how the above code works.

Greetings.

Re: show time between shift from last day

Hi,

I got this solved by actually at the end dragging down the formulas by hand. The formula I got and the columns where not fitting. So, now they have to do it manually (what is still faster than what they had before).

Greetings.

Re: show time between shift from last day

hi,
can this move forward?
Would be great to have it solved.

Greetings.

Re: show time between shift from last day

Hi,

Find the attached workbook sample.
It would be great to have it compatible with excel 2003 up to excel 2010.

Greetings.

Re: show time between shift from last day

Hi,
Sorry, from work I can't upload nothing
I will try to create one from home and upload it.

Greetings.

Re: Find empty cell in column A and paste data from the above cell in column A and F

Hi,

This worked really super
I had only to Row + 1 to make sure it would take the last empty field.

Now it works really nice and fast.

BIG thanks.

Hi all,

I need to show the time passed between the last shift. A person that works on shifts, should have at least 11 hours between the shifts.
I get the data in a sheet with all the people. Some work in shifts, others not. The ones that work on shift, the system puts out excel file and those shift workers appear in 2 rows instead of 1.

currently, we use the below formula, but we put this by hand and only on those that are in 2 rows of the excel file. I'm not able to fill it down as it would give is wrong times

=IF(A5="","",IF(A5=A4,IF(NETWORKDAYS(E4,E5)=2,B5-C4,""),""))

this is an example how it looks like:
[TABLE="width: 489"]

John Flonder

17/10/2016 08:35:33

17/10/2016 16:01:29

Sandra Romolek

17/10/2016 09:57:25

17/10/2016 18:03:44

[TD="align: right"]16:35:53
[/TD]

Sandra Romolek

18/10/2016 10:39:37

18/10/2016 18:04:26

Peter Flonder

17/10/2016 08:35:33

17/10/2016 16:01:29

[/TABLE]

As you can see, Sandra time between shifts was 16:35:53
Can this be done using VBA and then put the diference time in the G column?
(then name is in column A and the other fields in B and C)

It should be compatible with excel 2003 too

Greetings.

Re: converting numbers to hours minutes

OK, I got it solved

Dim rng As Range
Set rng = ActiveSheet.Range("D2:D500") '.SpecialCells(xlConstants, xlNumbers)
For Each c In rng
    If c.Value >= 1 And Int(c.Value) = c.Value Then _
    c.Value = (c.Value / 86400)
Next c
Columns("D:D").Select
Selection.NumberFormat = "mm:ss.0"

Silly me I did not see what you ment at the beginning.

Greetings.

Re: converting numbers to hours minutes

Hi Ray,

I still don't get it to work
I could use it as a formula and than drag it down and change the cell format to time, but if there could be VBA and a loop, the better for me as the report will have no formulas people could break.

Greetings.

Hi all,

I'm trying to find next empty cell in column A and copy paste the value from the above cell, as well the value from the above cell in column F.

If for example in column A a cell in row 12 is empty, the code should copy the value from the above cell and do the same in column F.

Any way this can be done?

Greetings.

Hi all,

I have a little problem converting numbers to hours and minutes with vba.
as for example, i have 583 minutes that would be in hours and minutes 09:43:00.
I tried to convert it with the below code:

Dim rng As Range
Set rng = ActiveSheet.Range("D2:D500").SpecialCells(xlConstants, xlNumbers)
For Each c In rng
    If c.Value >= 1 And Int(c.Value) = c.Value Then _
    c.Value = Round(c.Value / 60, 2)
Next c

But I get it like this: 9.72
If I try to format the cell, I will get: 17:16:48

Is there any way to adjust it to get it like this: 09:43:00

Greetings.

Re: Equal or less then, does not work when passing a date to SQL via vba for Teradata

Hi,
I actually had a more close look ot the column properties. It turns it is set as date (the MVT_DATE) and DECIMAL(9,0) the (Transaction_Date)
maybe there is the whole thing that when I pass the 20160831 in the (Transaction_Date), it works, but the date format is causing the (MVT_DATE) not to be seen.

Maybe this can help to figure out what is going on.

Greetings.

Re: Equal or less then, does not work when passing a date to SQL via vba for Teradata

hi, I used the above, but still the same. It errors like in the previous one and no chance to get this working.
The strange thing is, that when I pass a Between in the dates, it works fine

"WHERE ((TRANSACTION_DATE) Between " & FromDate & " And " & ToDate & ")AND ((BRANCH_NO)= " & branch & ") AND ((ACCOUNT_NO)= " & Acc & ") Order By TRANSACTION_DATE "

Wonder why the Between is working and not the <=

Greetings.