true power of MS excel. update: PowerBI

[pclogger]

Re: MS excel we give solutions. formula, codes and tutorials.

gayahin mo yung vba reference ko.

kung may nakacheck na missing iuncheck mo.

View attachment 1019809

TS di ko makita ung image , nakablock kasi dito sa office

 

[kirby21]

Re: MS excel we give solutions. formula, codes and tutorials.

TS di ko makita ung image , nakablock kasi dito sa office

panong nkblocked?

 

[pclogger]

Re: MS excel we give solutions. formula, codes and tutorials.

panong nkblocked?

nakablock sa PC ung mga image uploader site kaya di ko makita ung pinost mong image.
pahelp po ts dun s question ko space nalang po ung problem ko paano ko po mllgyan ng blank space ung bottom row every cut and paste ko ng data from sheet1 to sheeet 2 using macro

 

[neil115]

Re: MS excel we give solutions. formula, codes and tutorials.

Bro, eto ung code:

Check my version without hyperlinks but pure macros

>>

sorry late reply... pero ang galing mo sir... haha...ibang iba sa code ko..

d ko nga lang xadu nagets ung ibang code d2,,, bago p lang kase q sa macro eh...

tanung q lang sana d2 s part ng code moh...

lastrow = month * 14
firstrow = lastrow - 12

    Rows(firstrow & ":" & lastrow).Select
    Selection.EntireRow.Hidden = False

kung d po q nag kakamali e2 ung line ung nag dedetermine kung anung table ang papakita nya base s dropdown sa "Main" sheets... kung ayos lang po sayo pde po pa explain po 2?? sorry mdyo sobra n q mag tanung gusto ko lng matuto... hehe

 

[aubrey03]

Re: MS excel we give solutions. formula, codes and tutorials.

sorry late reply... pero ang galing mo sir... haha...ibang iba sa code ko..

d ko nga lang xadu nagets ung ibang code d2,,, bago p lang kase q sa macro eh...

tanung q lang sana d2 s part ng code moh...

lastrow = month * 14
firstrow = lastrow - 12

    Rows(firstrow & ":" & lastrow).Select
    Selection.EntireRow.Hidden = False

kung d po q nag kakamali e2 ung line ung nag dedetermine kung anung table ang papakita nya base s dropdown sa "Main" sheets... kung ayos lang po sayo pde po pa explain po 2?? sorry mdyo sobra n q mag tanung gusto ko lng matuto... hehe

lastrow = month * 14
firstrow = lastrow - 12

    Rows(firstrow & ":" & lastrow).Select
    Selection.EntireRow.Hidden = False

Yes, you are correct. Yang lines na yan ng code ang nagdedetermine kung anong table ung magiging visible, gumamit ako ng 3 variables jan.

month = eto yung number value ng selected month sa first sheet

lastrow = eto yung last row ng table na ipapakita dun sa isang sheet, "month * 14" ang formula nya kasi ung isang table including the headers has 14 rows.


firstrow = eto ung first row ng table, "lastrow - 12" ang formula nya kasi 12 rows ang difference between the header and the last row in any of the table in the other sheets.


If you will add agents dun sa other sheets, kelangan u iadjust ung formulas. Try mo iunhide yung lahat ng table dun sa other sheets then check ung row numbers ng mga table, makikita mo ung pattern.

 

[neil115]

Re: MS excel we give solutions. formula, codes and tutorials.

lastrow = month * 14
firstrow = lastrow - 12

    Rows(firstrow & ":" & lastrow).Select
    Selection.EntireRow.Hidden = False

Yes, you are correct. Yang lines na yan ng code ang nagdedetermine kung anong table ung magiging visible, gumamit ako ng 3 variables jan.

month = eto yung number value ng selected month sa first sheet

lastrow = eto yung last row ng table na ipapakita dun sa isang sheet, "month * 14" ang formula nya kasi ung isang table including the headers has 14 rows.


firstrow = eto ung first row ng table, "lastrow - 12" ang formula nya kasi 12 rows ang difference between the header and the last row in any of the table in the other sheets.


If you will add agents dun sa other sheets, kelangan u iadjust ung formulas. Try mo iunhide yung lahat ng table dun sa other sheets then check ung row numbers ng mga table, makikita mo ung pattern.

Rows(firstrow & ":" & lastrow).Select
    Selection.EntireRow.Hidden = False

e2 po sir?? anu po basa d2?? d q na kase magets pagdating sa part na &":"& .......

saka panu pag gnawa ko ung table is horizontally ung position o kaya pag 3 tables in one rows tapos nxt 3 tables ulit s next row??? papalitan ko lang ba ng columns ung rows??

 

[aubrey03]

Re: MS excel we give solutions. formula, codes and tutorials.

Rows(firstrow & ":" & lastrow).Select
    Selection.EntireRow.Hidden = False

e2 po sir?? anu po basa d2?? d q na kase magets pagdating sa part na &":"& .......

saka panu pag gnawa ko ung table is horizontally ung position o kaya pag 3 tables in one rows tapos nxt 3 tables ulit s next row??? papalitan ko lang ba ng columns ung rows??

"&" is a connector.

Let's say you selected january Kapag nagrun ung code na to:

"Rows(firstrow & ":" & lastrow).Select"

masasubstitute ung variables( i.e. firstrow = 2 and lastrow = 14 ) then the connector "&" will join them the character ":"

magiging gnito output nyan


Rows(2:14).select


If gagawin mong horizontal ung arrangement ng tables, you need to switch from rows() function to columns() function.


Hindi ko magets yung ibig mo sabihin na "pag 3 tables in one rows tapos nxt 3 tables ulit ", pakiclarify lang.


Better yet upload a sample file so I could check on it.

 

[neil115]

Re: MS excel we give solutions. formula, codes and tutorials.

"&" is a connector.

Let's say you selected january Kapag nagrun ung code na to:

"Rows(firstrow & ":" & lastrow).Select"

masasubstitute ung variables( i.e. firstrow = 2 and lastrow = 14 ) then the connector "&" will join them the character ":"

magiging gnito output nyan


Rows(2:14).select


If gagawin mong horizontal ung arrangement ng tables, you need to switch from rows() function to columns() function.


Hindi ko magets yung ibig mo sabihin na "pag 3 tables in one rows tapos nxt 3 tables ulit ", pakiclarify lang.


Better yet upload a sample file so I could check on it.

Sensya tagal ng reply q... hehe... tnx po sir... ung ibig q po svhn is ung ganito ng ayos ng table.. kaya po b na ifilter xa base nga po sa value dun s Main tab?

View attachment 211102

 

[aubrey03]

Re: MS excel we give solutions. formula, codes and tutorials.

Sensya tagal ng reply q... hehe... tnx po sir... ung ibig q po svhn is ung ganito ng ayos ng table.. kaya po b na ifilter xa base nga po sa value dun s Main tab?

View attachment 1023050

One table at a time lang ba gusto mo ishow or 3 tables ( i.e. January, February and March)?

 

[neil115]

Re: MS excel we give solutions. formula, codes and tutorials.

One table at a time lang ba gusto mo ishow or 3 tables ( i.e. January, February and March)?

One table lang sir... based po sa kung anung Month ung pipiliin ng user sa Data Validation s "Main" tab... kaya po b pag ganyan?

 

[aubrey03]

Re: MS excel we give solutions. formula, codes and tutorials.

One table lang sir... based po sa kung anung Month ung pipiliin ng user sa Data Validation s "Main" tab... kaya po b pag ganyan?

If one table at a time lang, why pa kelangan ibahin ung arrangement ng tables from vertical to horizontal? Same output lang


If you really want the table arranged vertically, I have a different approach instead of multiple tables that are hidden / shown using macros, we could just use one table and nested vlookup formulas.

Check attached file

 

[neil115]

Re: MS excel we give solutions. formula, codes and tutorials.

If one table at a time lang, why pa kelangan ibahin ung arrangement ng tables from vertical to horizontal? Same output lang


If you really want the table arranged vertically, I have a different approach instead of multiple tables that are hidden / shown using macros, we could just use one table and nested vlookup formulas.

Check attached file

aun vlookup pala hanap ko!... kaso d aq marunong na ng vlookup... hehe...

kaya q po sana gagawin na 3 tables per row lang is balak ko sana lagyan ng option na "View All Table"... para pag naka view all table na, madali na lang makikita lahat ng mga table kesa naka vertical lahat sila...

 

[aranparazo]

Re: MS excel we give solutions. formula, codes and tutorials.

TS paturo ng proper syntax sa vba

 

[nickiera]

Re: MS excel we give solutions. formula, codes and tutorials.

patambay ditto..Thanks

 

[diaven]

Re: MS excel we give solutions. formula, codes and tutorials.


guys baka alam nyo kung paano ito?

meron bang may alam dito kung paano ako makakagawa ng isang document (docx file) sa vba? I mean paragraph at may tamang formatting like indention, font style/size etc. yung mga nakalagay naman sa docx file manggagaling sa excel file. Meron akong nagawa kaso di maayos ang formatting

 

[knives645]

Re: MS excel we give solutions. formula, codes and tutorials.

guys baka alam nyo kung paano ito?

meron bang may alam dito kung paano ako makakagawa ng isang document (docx file) sa vba? I mean paragraph at may tamang formatting like indention, font style/size etc. yung mga nakalagay naman sa docx file manggagaling sa excel file. Meron akong nagawa kaso di maayos ang formatting

I-format mo agad ang document file mo. Bale yun na ang template mo.
Find and Replace lang naman ang gagawin na paraan dyan.

 

[99newbreed]

Re: MS excel we give solutions. formula, codes and tutorials.

hello po updated pa po ba yung thread na to?
pano po ang coding sa mga nasa picture paturo naman po.

 

[miroy]

Re: MS excel reportings, presentations, and vba helping thre

pa bm ts... thanks dito...

 

[kirby21]

Re: MS excel we give solutions. formula, codes and tutorials.

hello po updated pa po ba yung thread na to?
pano po ang coding sa mga nasa picture paturo naman po.

wala po code yun. formula lang po yun.

 

[threeofakind]

Re: MS excel we give solutions. formula, codes and tutorials.

pa bm po incase na kelanganin. salamat ts!