# Posts by megatronixs

• ## Sort date on descending from filter with vba

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?

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

Greetings.

• ## sum up the x in range to give proper percentage calculation

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.

• ## with SQL resutls containing a certain date, write yes or no

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

Hi all,

No possible solution?

Greetings.

• ## with SQL resutls containing a certain date, write yes or no

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:

Greetings.

• ## copy all excel files in all folders and subfolders based on excel sheet with file nam

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

Hi SO,

we managed to make it work

Greetings.

• ## copy all excel files in all folders and subfolders based on excel sheet with file nam

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

Hi all,

No possible solution for this?

Greetings.

• ## copy all excel files in all folders and subfolders based on excel sheet with file nam

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?

• ## change from date format to different date format

Hi all,

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

Code
``````' 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.

• ## show time between shift from last day

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.

• ## show time between shift from last day

Re: show time between shift from last day

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

Greetings.

• ## show time between shift from last day

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.

• ## show time between shift from last day

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.

• ## Find empty cell in column A and paste data from the above cell in column A and F

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.

• ## show time between shift from last day

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

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

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

[tr]

[td]

John Flonder

[/td]

[td]

17/10/2016 08:35:33

[/td]

[td]

17/10/2016 16:01:29

[/td]

[td][/td]

[/tr]

[tr]

[td]

Sandra Romolek

[/td]

[td]

17/10/2016 09:57:25

[/td]

[td]

17/10/2016 18:03:44

[/td]

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

[/tr]

[tr]

[td]

Sandra Romolek

[/td]

[td]

18/10/2016 10:39:37

[/td]

[td]

18/10/2016 18:04:26

[/td]

[td][/td]

[/tr]

[tr]

[td]

Peter Flonder

[/td]

[td]

17/10/2016 08:35:33

[/td]

[td]

17/10/2016 16:01:29

[/td]

[td][/td]

[/tr]

[/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.

• ## converting numbers to hours minutes

Re: converting numbers to hours minutes

OK, I got it solved

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 = (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.

• ## converting numbers to hours minutes

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.

• ## Find empty cell in column A and paste data from the above cell in column A and F

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.

• ## converting numbers to hours minutes

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:

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.

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

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.

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

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

Code
``"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.