Lowyat.NET: Latest topics by violette

Repeat Data into a Column

violette — Thu, 17 Nov 2011 20:39:26 +0800

hi, i have found code that can copy the same data into a column. here's the code;

CODE

With wbkSecond.Worksheets("Yearly")
.Range("B" & .Range("B" & Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteValues
Cells(1, 2) = DatePart("yyyy", Now)
Set copyRange = Worksheets("Yearly").Range("J5:J5000")
Range("B1").Copy copyRange
Set copyRange = Worksheets("Yearly").Range("K5:K5000")
Range("D1").Copy copyRange
End With

however, this code is for a fixed range from J5 to J5000. what should i do if i want the data to be repeated more dynamic?for example, it will be repeated according to the number of data in column B.if data in column B has 500 data, so the data in column J should be repeated until 500 data only.

Heart Beat Faster Than Usual

violette — Wed, 16 Nov 2011 01:31:59 +0800

This week is a rough week for me due to two important events. one is regarding my work and the other one is a sport tournament. i feel worried for both events and couldn't stop thinking bout it.then yesterday morning my heart beat was faster than usual. it feels like i have just finished 5km running. the only time i feel relax was when im in the court playing with my team member.

now i couldn't sleep and i could feel my heart beat is faster than usual. how can i make myself relax n slow down my heart beat?any idea why is this thing happening to me?am i sick or what?

Compare Sheets/File Name with Database

violette — Wed, 09 Nov 2011 10:26:09 +0800

Hi. I would like to do insert and update database data from excel file with the help of macro. before i can insert any data into the db, i have to check whether the field is already in the database or not. the primary key is year_id, period_id and stock_code. the data for year_id and period_id in excel file is according to the sheet's name. for example; there are 5 sheets; yearly, quarter 1, quarter 2, quarter 3, and quarter 4. and the data for each company is arranged according to the sheets. how to compare the sheet name and file name with the field in the database for the checking purposes?

sheet name || period_id
quarter 1 || 1
quarter 2 || 2
quarter 3 || 3
quarter 4 || 4
yearly || 5

meanwhile for the year_id, it should take the name of the file. for example,

file name || year_id
year2008 || 2008
year2009 || 2009
year2010 || 2010

Query for Year and Period

violette — Tue, 08 Nov 2011 14:38:35 +0800

I need to get data between year and period. 1 year have four periods.
i.e; year = 2010 period = 1,2,3,4
the problem is on the period part where i could not select second period smaller from the first period.
for example, i choose year=2010 to year=2011
and period=4 to period=2
the result should display year=2010 period=4,year=2011 period=1,year=2011 period 2. but when i run the code, it returns nothing. however if i choose year=2010 to year=2011 and period=1 to period=4, the code works fine.

here's the example of my code;

CODE

SELECT SUM(ma5.alert_value)
FROM company_alert ma5
LEFT JOIN qiddb.company_info mb5 ON (mb5.stock_code=ma5.stock_code)
LEFT JOIN ref_sector mc5 ON (mc5.sector_code=mb5.sector_code)
WHERE ma5.alert_id='VPAEH' AND mc5.sector_code=ref.sector_code AND ma5.year_id BETWEEN '2009' AND '2010' AND ma5.period_id BETWEEN 'q3' AND 'q1'

which part should i fix to make it right? thanks all

Copy multiple sheets

violette — Thu, 27 Oct 2011 12:18:48 +0800

I need to copy multiple sheets into the template accordingly. well, i manage to copy for the first sheet only. i've tried to modify the code to copy multiple sheets at one time and i couldnt get it right. here's the code for copying the first sheet into the template. i have to specify the column as i just need certain column to be copied for that particular sheet.

i have five sheets; Year, Q1, Q2, Q3, Q4

CODE

Sub Copy()

Dim wbk As Workbook
Dim strFirstFile As String
Dim strSecondFile As String

strFirstFile = "C:\Documents and Settings\user\My Documents\FiST Mac\Data.xls"
strSecondFile = "C:\Documents and Settings\user\My Documents\FiST Mac\FiST_data_template.xls"

Set wbk = Workbooks.Open(strFirstFile)
With wbk.Sheets("Year")
.Range(.Range("A5:AJ5"), .Range("A65536").End(xlUp)).Copy

End With

Set wbk = Workbooks.Open(strSecondFile)
With wbk.Sheets("Yearly")

Sheets("Yearly").[B65536:AK65536].End(xlUp)(2).PasteSpecial Paste:=xlValues

End With

Application.DisplayAlerts = False
wbk.SaveAs "C:\Documents and Settings\user\My Documents\FiST Mac\FISTdb.xls"
Windows("Data.xls").Close
MsgBox "FiST Database Updated", vbOKOnly, " FiST"

Application.DisplayAlerts = True

End Sub

Update Database(mysql) using record from Excel

violette — Tue, 25 Oct 2011 16:22:55 +0800

I would like to have a macro to export data from an excel file into the database(SQL) for insert and update data into existing tables purposes. but i'm not sure how to do this.

can anyone suggest me or give some ideas on how to make it possible? thanks a lot ur help is much appreciated.

vb.net

violette — Thu, 06 Oct 2011 16:35:08 +0800

hi all! i'm not sure where to post this. i hope i'm posting at the right section.

previously i use formula as below;
receivable * 365 / revenue

n here is how it looks like in vb.net coding.

» Click to show Spoiler - click again to hide... «

note that the 'receivable' and 'revenue' are taken from database(mysql)

however,if the formula of a calculation involve data for current year divide by data from previous year, how should i implement it?
for example;

revenue from current year * 365 / revenue from previous year

any idea?

Cannot search from 'Start' menu

violette — Fri, 02 Sep 2011 17:05:54 +0800

Hye all. I hope I'm posting at the right place. few days ago i've run the tune-up utilities. did some clean up. but then when i type in the search box at the 'Start' menu, i could not find the program. even though i have type in the full name of the program. luckily im using rocketdock and got shortcuts for certain programs that is frequently being used. anyone know why does this problem happened? is it because of the clean up thingy that i've done previously? or i accidentally change any settings?

Autonumber for Datagrid

violette — Mon, 08 Aug 2011 09:26:26 +0800

i would like to add a new column that will generate autonumber to the existing datagrid. based on my research i got from google, i only manage to create the new column(but it is empty). hope someone will help me to solve my problem. here's my code. please have a look and guide me.

CODE

<asp:DataGrid id="DataGrid1" runat="server" BorderStyle="None" AutoGenerateColumns="False" Width="950px" BackColor="White" BorderColor="#3366CC" BorderWidth="1px" CellPadding="4" HorizontalAlign="Center" EmptyDataText="There are no data records to display." EnableTheming="True" DataSourceID="BrokerRanking" Font-Names="calibri" Font-Size="16px">
<HeaderStyle ForeColor="White" />
<Columns>
<asp:TemplateColumn HeaderText="No">
<HeaderStyle BackColor="#2B1B17" Font-Bold="True" HorizontalAlign="Center" Font-Names="calibri" Font-Size="16px" Width="10px" />
<ItemStyle BackColor="White" Font-Bold="False" Font-Italic="False" Font-Names="Calibri"
Font-Overline="False" Font-Strikeout="False" Font-Underline="False" ForeColor="Black" />

</asp:TemplateColumn>

<asp:BoundColumn DataField="PName" ReadOnly="True" HeaderText="Name" DataFormatString=" {0:N2}">
<HeaderStyle BackColor="#2B1B17" Font-Bold="True" HorizontalAlign="Center" Font-Names="calibri" Font-Size="16px" Width="150px" />
<ItemStyle Font-Bold="False" Font-Italic="False" Font-Overline="False" Font-Strikeout="False"
Font-Underline="False" HorizontalAlign="Left" />
</asp:BoundColumn>
<asp:BoundColumn DataField="totalVolume" ReadOnly="True" HeaderText="Vol ('mil)" DataFormatString=" {0:N2}">
<HeaderStyle BackColor="#2B1B17" Font-Bold="True" HorizontalAlign="Center" Font-Names="calibri" Font-Size="16px" Width="100px" />
<ItemStyle Font-Bold="False" Font-Italic="False" Font-Overline="False" Font-Strikeout="False"
Font-Underline="False" HorizontalAlign="Right" />
</asp:BoundColumn>
<asp:BoundColumn DataField="PercentVolume" ReadOnly="True" HeaderText="%" DataFormatString=" {0:N2}">
<HeaderStyle BackColor="#2B1B17" Font-Bold="True" HorizontalAlign="Center" Font-Names="calibri" Font-Size="16px" Width="100px" />
<ItemStyle Font-Bold="False" Font-Italic="False" Font-Overline="False" Font-Strikeout="False"
Font-Underline="False" HorizontalAlign="Right" />
</asp:BoundColumn>

</Columns>

</asp:DataGrid>

[addedon]August 9, 2011, 11:35 am[/addedon]i have found the solution guys. just add in this simple line to the column and it will generate the numbers accordingly. here's the code;

CODE

<asp:TemplateColumn HeaderText="No">
<HeaderStyle BackColor="#2B1B17" Font-Bold="True" HorizontalAlign="Center" Font-Names="calibri" Font-Size="16px" Width="10px" />
<ItemStyle BackColor="White" Font-Bold="False" Font-Italic="False" Font-Names="Calibri"
Font-Overline="False" Font-Strikeout="False" Font-Underline="False" ForeColor="Black" />

<itemtemplate>
<%# (DataGrid1.PageSize*DataGrid1.CurrentPageIndex)+ Container.ItemIndex+1%>
</itemtemplate>

</asp:TemplateColumn>

girl's attitude that makes you annoyed

violette — Thu, 08 Oct 2009 14:00:07 +0800

i admit that everyone have weaknesses, as no body's perfect in this world. maybe we can share our experience here and how we can manage it so that friends, are still friends. some people might show it directly if he/she feels annoyed and some people just keep it inside and act as usual. somehow, at one time you will fell like -AARRGGHHH!! cannot tahan ady

auto shutdown

violette — Fri, 31 Jul 2009 15:57:35 +0800

Guys, Im facing this problem since last week and I really have no idea why and what is the cause of this problem. No matter whether I use it or just leave it on standby mode, it will switches off itself. Then, when I try to turn it on, 'windows error recovery' will be displayed. I've sent my laptop to nearest service center at Ipoh and they told me something wrong with the motherboard and it will cost me rm800++ if I want them to get the new one. Today, I went back to that shop and I told them I just wanna take my laptop. Just save money and buy new one is much better. Then, I simply try switch on my laptop. And it shutdown itself. Again! Its ok.. I just wait few more miinutes, and try again. Then the 'windows error recovery' displayed and I chose "SAFE MODE WITH NETWORKING". And now I can use my laptop as usual [but using safe mode la.] So. Does anyone know what is happening actually? Im a lil bit confused now...

help me

violette — Thu, 28 May 2009 20:06:48 +0800

Hello everyone. Recently I just noticed bout this problem. If I use headphone or earphone, any music play still can be heard from the laptop speaker and I hear nothing from the headphone. Only if i click on something, the click sound can be heard from the headphone. What does it mean? Something wrong with the setting or what? Help me please...
oh ya. my laptop is compaq presario f700

help me with my laptop problem

violette — Thu, 28 May 2009 13:15:38 +0800

help me making calendar

violette — Sun, 10 May 2009 12:45:53 +0800

hye guys~*
i need help here, actually this question was given by my lecturer which acquired me to make a calendar that will start with 1st of Jan according to the letter what user want. i didnt get it right-all months will start with the letter i've entered and every month got 31 days. i've ruined the calendar

can u guys help me out solving it?actually im scared if my thread will be closed by mod

this is my code>>>

#include <stdio.h>
#include <conio.h>

int main()
{
int i,j;
char day;

printf("Please enter first day of the year 2009 >>");
scanf("%c",&day);

switch (day)
{
case 'M':
case 'm':

for(i=1;i<=12;i++)

{
printf("\nMONTH %d",i);
printf("\nMo \tTu \tWe \tTh \tFr \tSa \tSun\n");
for(j=1;j<=31;j++)
printf("%d\t",j);
}
break;
case 'T':
case 't':
for(i=1;i<=12;i++)

{
printf("\nMONTH %d",i);
printf("\nMo \tTu \tWe \tTh \tFr \tSa \tSun\n");
for(j=1;j<=31;j++)
printf("\t%d",j);
}
break;
case 'W':
case 'w':

for(i=1;i<=12;i++)

{
printf("\nMONTH %d",i);
printf("\nMo \tTu \tWe \tTh \tFr \tSa \tSun\n");
for(j=1;j<=31;j++)
printf("\t%d",j);
}
break;
case 'H':
case 'h':

for(i=1;i<=12;i++)

{
printf("\nMONTH %d",i);
printf("\nMo \tTu \tWe \tTh \tFr \tSa \tSun\n");
for(j=1;j<=31;j++)
printf("\t%d",j);
}
break;
case 'F':
case 'f':

for(i=1;i<=12;i++)

{
printf("\nMONTH %d",i);
printf("\nMo \tTu \tWe \tTh \tFr \tSa \tSun\n");
for(j=1;j<=31;j++)
printf("\t%d",j);
}
break;
case 'S':
case 's':

for(i=1;i<=12;i++)

{
printf("\nMONTH %d",i);
printf("\nMo \tTu \tWe \tTh \tFr \tSa \tSun\n");
for(j=1;j<=31;j++)
printf("\t%d",j);
}
break;
case 'U':
case 'u':

for(i=1;i<=12;i++)

{
printf("\nMONTH %d",i);
printf("\nMo \tTu \tWe \tTh \tFr \tSa \tSun\n");
for(j=1;j<=31;j++)
printf("\t%d",j);
}
break;

default:
printf("\nSorry, you have enter the wrong input.");

}
getch();
return(0);
}

my other prob is, i dont know how to make the date follow the column of the day.
i got 10 days each row but the column only have 7 due to 7 days in each month

come2 help me