true power of MS excel. update: PowerBI

[romcel11]

Re: MS excel formula, programming, analysis, presentation and tutorials.

kung kgs sya dumating, at grams ang pag consume. edi i-input mo as decimal value ung pa consume
lets say, 2kgs of ampalaya ang dumating. eh ng consume ng 300 grams,
i-le-less mo sa stocks ung .3 kgs. sa bawat serving. kung apat [4x0.3] = 1.2kgs.
ending meron ka pang .8 kgs for that day.

got it

 

[voyage18]

Re: MS excel formula, programming, analysis, presentation and tutorials.

pwede din yan sir romcel.. ang problema lang po kase sir panu ang gagamitin kong program? need po ba ng macro or anything na formula para automatic po ang output.

Ampalaya - Bahaykubo 30g
Ampalaya - Binagoongan 50g
Ampalaya - Diningdeng 30g
Ampalaya - Sutanghon Gisado 250g
Baguio beans - Bihon No. 8 24g
Baguio beans - Bihon No. 9 36g
Baguio beans - Bihon No. 11 48g
Baguio beans - Bihon No. 12 60g
Baguio beans - Bihon No. 13 75g
Baguio beans - Bihon No. 14 105g
Baguio beans - Bihon No. 18 180g
Baguio beans -Canton No. 8 24g

yan sir ang halimbawa ng mga grams bawat gulay

 

[azter]

Re: MS excel formula, programming, analysis, presentation and tutorials.

Hi mga masters, pa-tulong po ako:

1. Cell value under dates will be updated based on specified Start and End dates range.
2. Ung highlighting is automatic thru conditional formatting if cell value is = "w" and "x".


View attachment 341827:

Sa formula, need ko malaman kung sunday ung date.
Ginamitan ko na ng OR at WEEKDAY() function, example:
=IF(AND(OR(J$2>=$D39,J$2<=$E39),WEEKDAY(J2)=1),"w","x")
* tanggap ung formula pero bugged naman
* ayaw kong tanggalin ung Sunday dates kasi paano naman kung holiday, kaya nais ko na completo ung dates para sa condition na Sundays="x"
* ayaw ko magmanual input kais hassle at gusto automatic dahil may COUNTIF formula ako under "Duration Days" para ma-count ko actual woking days which is = w

* magmanual muna ako sa holiday, hehe

Thanks In Advance!

 

[romcel11]

Re: MS excel formula, programming, analysis, presentation and tutorials.

Hi mga masters, pa-tulong po ako:

Sa formula, need ko malaman kung sunday ung date.
Ginamitan ko na ng OR at WEEKDAY() function, example:
=IF(AND(OR(J$2>=$D39,J$2<=$E39),WEEKDAY(J2)=1),"w","x")
* tanggap ung formula pero bugged naman
* ayaw kong tanggalin ung Sunday dates kasi paano naman kung holiday, kaya nais ko na completo ung dates para sa condition na Sundays="x"
* ayaw ko magmanual input kais hassle at gusto automatic dahil may COUNTIF formula ako under "Duration Days" para ma-count ko actual woking days which is = w
* magmanual muna ako sa holiday, hehe
Thanks In Advance!

Boss, try mong i-print kung sunday ung date using:

=TEXT([DATE_MANUAL_INPUT_OR_CELL],"dddd")

So to check kung sunday sya:

=AND(TEXT(A2,"dddd")="Sunday")

para sa code mong:

=IF(AND(OR(J$2>=$D39,J$2<=$E39),WEEKDAY(J2)=1),"w" ,"x")

palitan naten ng:

=IF(AND(J$2>=$D39,J$2<=$D39,NOT(TEXT(J$2,"dddd")="Sunday")),"w","x")

Tungkol naman dun sa mga holidays, mg gwa ka n lang ng ibang worksheet tapos ilista mo n lang dun.
Di ko sure, pero tingin ko pede ata i check/pagcompare using MATCH(). kpag meron match bigsabihin
holiday ung araw na yon.


OFFTOPIC: Gaunt chart to?

 

[azter]

Re: MS excel formula, programming, analysis, presentation and tutorials.

@romcel11 Bugged pa rin idol,

1. Cell value under dates will be updated based on specified Start and End dates range.
- dapat sa range ng start at end date mo lamang na magcontain ng values "w" and "x", if not in range dapat no value ("") while checking range of dates if Sunday then mark it as "x".

=IF(AND(J$2>=$D39,J$2<=$E39,NOT(TEXT(J$2,"dddd")="Sunday")),"w","x")

same as

=IF(AND(J$2>=$D39,J$2<=$E39,NOT(WEEKDAY(J$2)=1)),"w","x")

This works because WEEKDAY returns a number 1-7 that corresponds to a given day of the week. With default settings, Sunday = 1 and Saturday = 7. See WEEKDAY for more info. reference from: https://exceljet.net/formula/get-day-name-from-date

Tungkol naman dun sa mga holidays, mg gwa ka n lang ng ibang worksheet tapos ilista mo n lang dun.
Di ko sure, pero tingin ko pede ata i check/pagcompare using MATCH(). kpag meron match bigsabihin
holiday ung araw na yon.

tama ka, wag muna sa ngayon. i-manual input ko ung holiday kasi pwede ka pumasok for double pay.

OFFTOPIC: Gaunt chart to?

yup "Gantt Chart", excel pwede na para easy edit without the use of primavera p6 or any other software although my export to xls features ang mga to.

 

[romcel11]

Re: MS excel formula, programming, analysis, presentation and tutorials.

@romcel11 Bugged pa rin idol,
This works because WEEKDAY returns a number 1-7 that corresponds to a given day of the week. With default settings, Sunday = 1 and Saturday = 7. See WEEKDAY for more info. reference from: https://exceljet.net/formula/get-day-name-from-date

boss eto na ung code, i-blank naten ung mga dates na outside nung start and end dates:

=IF(OR(J$2<=$D39,J$2>=$E39),"",IF(AND(TEXT(J$2,"dddd")="Sunday"),"x","w"))

@romcel11 Bugged pa rin idol,
tama ka, wag muna sa ngayon. i-manual input ko ung holiday kasi pwede ka pumasok for double pay.

double pay = more beer

@romcel11 Bugged pa rin idol,
yup "Gantt Chart", excel pwede na para easy edit without the use of primavera p6 or any other software although my export to xls features ang mga to.

Gantt Chart pala spelling. TY

 

[azter]

Re: MS excel formula, programming, analysis, presentation and tutorials.

=IF(OR(J$2<=$D39,J$2>=$E39),"",IF(AND(TEXT(J$2,"dddd")="Sunday"),"x","w"))

Gumana ung code mo idol, kaso curious lang ako kung bakit -1 sa starting date at end date?
example:
start date: March 01 magiging March 02
end date: March 30 magiging March 29

Heto ginawa ko, pa-check lang kung malinis, haha

=IF(OR(J$2<=$D39[COLOR="#FF0000"]-1[/COLOR],J$2>=[COLOR="#FF0000"]1+[/COLOR]$E39),"",IF(AND(TEXT(J$2,"dddd")="Sunday"),"x","w"))

double pay = more beer

Oo naman idol!

 

[romcel11]

Re: MS excel formula, programming, analysis, presentation and tutorials.

@Voyage, bossing eto na ung workbook mo. mainit-init pa! ahahahaha
Explain ko lang den.


Sheet#1: Material List

Listahan to ng mga materials na i-a-account mo.
Lahat ng mga raw materials mo idagdag mo dito.
lagyan mo lang ng unique na material code for easy referencing.

Sheet#2: MenuList

Listahan to ng mga dish na sine-serve mo.
i-lista mo sila lahat dito bigyan ng dishcode.
tapos kung gagamit ng multiple na raw materials para mkagawa ng isang dish,
gamitin ang parehong dishcode.
LAGYAN DIN NG QTY na nako-consume sa isang serving.

Sheet#3: ReceiveList

Dito naka-list ung mga items na nare-receive mo everytime
na merong delivery. para magkaron ng stock ung inventory system.

Sheet#4: OrderList

Listahan ng mga orders ng customer.
ilagay kung anong date inorder, anong table ang umorder
anong dish ang inorder at ilang serving ang inorder.

Sheet#5: Summary(Daily)

Dito makikita ung na consume na rawmaterials for a specified date.

dun sa sheet#1: don ko n lang nilagay ung simple summary between two dates
Goodluck!

=IF(OR(J$2<=$D39,J$2>=$E39),"",IF(AND(TEXT(J$2,"dddd")="Sunday"),"x","w"))

Gumana ung code mo idol, kaso curious lang ako kung bakit -1 sa starting date at end date?
example:
start date: March 01 magiging March 02
end date: March 30 magiging March 29

@azter, master good thing nka tulong ako. sablay lang ng konte. ahahaa

EDIT: remove mo lang ung mga equal sign dun sa unang if na under ng OR() function.
magiging output nya:

=IF(OR(J$2<$D39,J$2>$E39),"",IF(AND(TEXT(J$2,"dddd")="Sunday"),"x","w"))

 

[voyage18]

Re: MS excel formula, programming, analysis, presentation and tutorials.

sir romcel thanks,,

Sir pa check ng file ko. baka may mas maganda pa po.. okay po ang ginawa nio sir...

 

[romcel11]

Re: MS excel formula, programming, analysis, presentation and tutorials.

@voyage,, sir epektib ung excel? Sige, itest mo lang yan. Kpag my new prob ka. Blik ka dto pra ma help ka nmen

 

[voyage18]

Re: MS excel formula, programming, analysis, presentation and tutorials.

@voyage,, sir epektib ung excel? Sige, itest mo lang yan. Kpag my new prob ka. Blik ka dto pra ma help ka nmen

okay naman po sya sir,, kaya lang,, pwede pa check nung ginawa ko po baka ma iimprove pa sya sir.. need ko po ng mga reports eh like critical, tapos ung mga dapat bilhin kapag critical na at how much or ilan ang bibilhin etc etc. ty po sir

 

[LightHole24]

Re: MS excel formula, programming, analysis, presentation and tutorials.

Eto na naman ako master romcell11 nagbabalik Patulong po uli

Bali yung code na sinabi mo dito sir ay hindi gumana, may error po

With Target
  .Parent.Range("G" & .Row).Comment.Text = "This range is " & .Offset(-1, 0).Value - .Value
End With

So sinubukan uli ni dodong kung kaya ba niya ayusin. So far kahit pano nagawa naman ni dodong po. Every time na maglalagay ako ng new value sa range ng cell, magkakaroon din ng comment yung TOTAL value per day. Ang problema lang, sa IBANG cell napupunta yung comment, kahit naka target.range("G" & target.row) chuchu naman ako, dun napupunta yung comment sa column I. Sa debug niya kapag nira-run ko yung code, tama naman sana yung row na lumalabas sa debug. Pero kapag magdi-display na sa excel CELL, sa column I napupunta yung comment! Ahahaha. Buset Ang masaklap pa nito, sa kada lagay ko ng new value sa earnings ko per day sa column C to F, nagkakaroon ng comment yung supposed to be TOTAL EARNINGS ng supposed to be column G, doon sa column I J K L!!! At kapag naglagay ako ng new value sa susunod na araw (new row), yung comment sa total earnings na nasa column I to L ay malalagay din sa new row sa baba nila

Ang gusto ko po sana mangyari ay gaya lang ng mga column ko sa cell C to F na kapag naglagay ako ng value ay nandon lang din yung comment sa cell na yun. At kapag nag delete ako ng value ay mababawasan din yung value ng total cell

Ito naman yung code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rTarget As Range        '<~ range will hold target. old habits never die.
Dim dblValHolder As Double  '<~ will hold the value of rtarget.
Dim dblResultValue As Double   '<~ will hold the current value
Dim dblTotalValue As Double
Dim dblPreviousDayTotal As Double

'<~ to ensure na ung nagbagong cell ay within the table
If Intersect(Target, Range("C4:G30")) Is Nothing Then Exit Sub

Set rTarget = Target    '<~ pass target to the declared variable

'<~ to ensure na isang cell lang ung nagbago.
'<~ incase na ng delete ka ng multiple cell within the table,
'<~ then exit sub na.
If rTarget.Cells.Count > 1 Then Exit Sub

Application.EnableEvents = False            '<~ disable naten events. bka mgtrigger ung ibang cell events.

With rTarget
  '<~i-hold natin ung value na ininput mo
  If Not IsNumeric(.Value) Then Exit Sub    '<~ exit sub tayo kapag text ang ininput.
  If Not IsNumeric(.Offset(-1, 0).Value) Then Exit Sub '<~ ganon din kung hindi number ung sa taas.
  dblValHolder = CDbl(.Value)               '<~ pass naten sa variable
  
    If dblValHolder = 0 Or rTarget.Value = "" Then '<~ naglagay ako validation kung may value ba yung cell or empty
        rTarget.ClearContents
    Else
        dblResultValue = Abs(.Offset(-1, 0).Value - dblValHolder) '<~ do the math.
        dblPreviousDayTotal = Range("G" & Target.Row).Offset(-1, 0)
        dblTotalValue = Abs((Range("G" & Target.Row).Value) - dblPreviousDayTotal)
    End If
End With

For Each rTarget In Target
    If rTarget.Comment Is Nothing And rTarget.Value = "" Then   '<~ kung walang laman ang cell, wala ring comment
        rTarget.ClearContents
    
    ElseIf rTarget.Comment Is Nothing And rTarget.Value <> "" Then  '<~ kung may laman ang cell, mayroon ring comment
        With rTarget.AddComment
            .Visible = False
            .Text "Current Value Today: " & rTarget.Value & vbNewLine & "Current Earnings Today: " & dblResultValue
        End With
        
        With rTarget.Range("G" & rTarget.Row).AddComment '<~ add comment of total value to CELL G current row
            .Visible = False
            .Text "Total Value Today: " & dblTotalValue & vbNewLine & "Previous Day Total: " & dblPreviousDayTotal
        End With
            
    ElseIf Not rTarget.Comment Is Nothing And rTarget.Value <> "" Then      '<~ kapag nag edit ako ng value ng cell
        With rTarget.Comment
            .Visible = False
            .Text "Current Value Today: " & rTarget.Value & vbNewLine & "Current Earnings Today: " & dblResultValue
        End With
        
        With rTarget.Range("G" & rTarget.Row).AddComment '<~ add comment of total value to CELL G current row
            .Visible = False
            .Text "Total Value Today: " & dblTotalValue & vbNewLine & "Previous Day Total " & dblPreviousDayTotal
        End With
    
    ElseIf Not rTarget.Comment Is Nothing And rTarget.Value = "" Then       '<~ kung may laman ang cell, pero nag delete ako ng value sa cell, wala ring comment
        rTarget.ClearContents
        rTarget.Comment.Delete
        
        rTarget.Range("G" & rTarget.Row).Comment.Delete
        rTarget.Range("G" & rTarget.Row).ClearContents
    End If
Next

Application.EnableEvents = True             '<~ enable naten ulit.

End Sub

PS: Mali yung formula ko sa pinakatotal earnings sa baba. Pero hindi yun ang issue ko po. Yung sa comment

 

[romcel11]

Re: MS excel formula, programming, analysis, presentation and tutorials.

@lighthole,

ung line mo na:

For Each rTarget In Target

ibig sabihin ginamit mo ung rTarget na variable to loop through cells under Target variable.
kaya ngbabago tlga ung cell reference.

try mo n lang ilagay ung code na bngay ko bago mag-loop.

EDIT: Yun! na-gawan din ng paraan! mali pala tayo dun sa pag refer ng Column G.
dapat kung gagamitin naten ung rTarget na variable para magreference to Column G
add tayo ng .parent object sa kanya. this tells the excel that we are working with the
sheet where rTarget and Column G is.

so ang bagong code ni dodong:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rTarget As Range        '<~ range will hold target. old habits never die.
Dim dblValHolder As Double  '<~ will hold the value of rtarget.
Dim dblResultValue As Double   '<~ will hold the current value
Dim dblTotalValue As Double
Dim dblPreviousDayTotal As Double

'<~ to ensure na ung nagbagong cell ay within the table
If Intersect(Target, Range("C4:G30")) Is Nothing Then Exit Sub

Set rTarget = Target    '<~ pass target to the declared variable

'<~ to ensure na isang cell lang ung nagbago.
'<~ incase na ng delete ka ng multiple cell within the table,
'<~ then exit sub na.
If rTarget.Cells.Count > 1 Then Exit Sub

Application.EnableEvents = False            '<~ disable naten events. bka mgtrigger ung ibang cell events.

With rTarget
  '<~i-hold natin ung value na ininput mo
  If Not IsNumeric(.Value) Then Exit Sub    '<~ exit sub tayo kapag text ang ininput.
  If Not IsNumeric(.Offset(-1, 0).Value) Then Exit Sub '<~ ganon din kung hindi number ung sa taas.
  dblValHolder = CDbl(.Value)               '<~ pass naten sa variable
  
    If dblValHolder = 0 Or rTarget.Value = "" Then '<~ naglagay ako validation kung may value ba yung cell or empty
        .ClearContents
    Else
        dblResultValue = Abs(.Offset(-1, 0).Value - dblValHolder) '<~ do the math.
        dblPreviousDayTotal = .Parent.Range("G" & .Row).Offset(-1, 0)
        dblTotalValue = Abs((.Parent.Range("G" & .Row).Value) - dblPreviousDayTotal)
    End If
End With
 
 '////////////////////////////////////////////////////////////////////////////
 '////////////////////////////////////////////////////////////////////////////
 '///////////////                                              ///////////////
 '///////////////  DODONG DITO NG SIMULA UNG CHANGES NG CODES  ///////////////
 '///////////////                                              ///////////////
 '////////////////////////////////////////////////////////////////////////////
 '////////////////////////////////////////////////////////////////////////////
 
 
On Error Resume Next            '<~ disable raising error. para magwork ung code sa baba
rTarget.Comment.Delete          '<~ regardless kung my comment or wala. tell excel na i-try nyang i-delete ung comment

If Len(Trim(rTarget.Value)) > 0 Then    '<~ checks if kung ung bagong value ay merong laman. kung ng delete skip sya dito.
  rTarget.AddComment                    '<~ add comment kasi my laman eh.
  rTarget.Comment.Text "Current Value Today: " & rTarget.Value & vbNewLine & "Current Earning Today: " & dblResultValue
End If

With rTarget.Parent.Range("G" & rTarget.Row)
  .Comment.Delete               '<~ delete commend sa column G. dahil dun sa resume next. hnd to mg re-raise ng error
  .AddComment                   '<~ add comment.
  .Comment.Text "Total Value Today: " & dblTotalValue & vbNewLine & "Previous Day Total: " & dblPreviousDayTotal
End With
Application.EnableEvents = True             '<~ enable naten ulit.

End Sub

Test mo agad idol para ma-check naten kung working o hinde.

 

[LightHole24]

Re: MS excel formula, programming, analysis, presentation and tutorials.

@lighthole,

ung line mo na:

For Each rTarget In Target

ibig sabihin ginamit mo ung rTarget na variable to loop through cells under Target variable.
kaya ngbabago tlga ung cell reference.

try mo n lang ilagay ung code na bngay ko bago mag-loop.

EDIT: Yun! na-gawan din ng paraan! mali pala tayo dun sa pag refer ng Column G.
dapat kung gagamitin naten ung rTarget na variable para magreference to Column G
add tayo ng .parent object sa kanya. this tells the excel that we are working with the
sheet where rTarget and Column G is.

so ang bagong code ni dodong:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rTarget As Range        '<~ range will hold target. old habits never die.
Dim dblValHolder As Double  '<~ will hold the value of rtarget.
Dim dblResultValue As Double   '<~ will hold the current value
Dim dblTotalValue As Double
Dim dblPreviousDayTotal As Double

'<~ to ensure na ung nagbagong cell ay within the table
If Intersect(Target, Range("C4:G30")) Is Nothing Then Exit Sub

Set rTarget = Target    '<~ pass target to the declared variable

'<~ to ensure na isang cell lang ung nagbago.
'<~ incase na ng delete ka ng multiple cell within the table,
'<~ then exit sub na.
If rTarget.Cells.Count > 1 Then Exit Sub

Application.EnableEvents = False            '<~ disable naten events. bka mgtrigger ung ibang cell events.

With rTarget
  '<~i-hold natin ung value na ininput mo
  If Not IsNumeric(.Value) Then Exit Sub    '<~ exit sub tayo kapag text ang ininput.
  If Not IsNumeric(.Offset(-1, 0).Value) Then Exit Sub '<~ ganon din kung hindi number ung sa taas.
  dblValHolder = CDbl(.Value)               '<~ pass naten sa variable
  
    If dblValHolder = 0 Or rTarget.Value = "" Then '<~ naglagay ako validation kung may value ba yung cell or empty
        .ClearContents
    Else
        dblResultValue = Abs(.Offset(-1, 0).Value - dblValHolder) '<~ do the math.
        dblPreviousDayTotal = .Parent.Range("G" & .Row).Offset(-1, 0)
        dblTotalValue = Abs((.Parent.Range("G" & .Row).Value) - dblPreviousDayTotal)
    End If
End With
 
 '////////////////////////////////////////////////////////////////////////////
 '////////////////////////////////////////////////////////////////////////////
 '///////////////                                              ///////////////
 '///////////////  DODONG DITO NG SIMULA UNG CHANGES NG CODES  ///////////////
 '///////////////                                              ///////////////
 '////////////////////////////////////////////////////////////////////////////
 '////////////////////////////////////////////////////////////////////////////
 
 
On Error Resume Next            '<~ disable raising error. para magwork ung code sa baba
rTarget.Comment.Delete          '<~ regardless kung my comment or wala. tell excel na i-try nyang i-delete ung comment

If Len(Trim(rTarget.Value)) > 0 Then    '<~ checks if kung ung bagong value ay merong laman. kung ng delete skip sya dito.
  rTarget.AddComment                    '<~ add comment kasi my laman eh.
  rTarget.Comment.Text "Current Value Today: " & rTarget.Value & vbNewLine & "Current Earning Today: " & dblResultValue
End If

With rTarget.Parent.Range("G" & rTarget.Row)
  .Comment.Delete               '<~ delete commend sa column G. dahil dun sa resume next. hnd to mg re-raise ng error
  .AddComment                   '<~ add comment.
  .Comment.Text "Total Value Today: " & dblTotalValue & vbNewLine & "Previous Day Total: " & dblPreviousDayTotal
End With
Application.EnableEvents = True             '<~ enable naten ulit.

End Sub

Test mo agad idol para ma-check naten kung working o hinde.

Grabe ngayon ko lang na test bossing at wow, saktong sakto!!! Gumana master!

Marami pong salamat hehehe. At pinaikli mo yung code nice. Balitaan kita pag may sakaling error sa pag gamit ko or may gusto ako idagdag haha

May ma-re-recommend ka ba na website kung san pwede ko aralin tong vba from beginner to advance? Natuwa lang ako ayos pala tong vba sa excel hehe

 

[romcel11]

Re: MS excel formula, programming, analysis, presentation and tutorials.

para sa course ng vba eto ung binasa ko:

http://www.excel-pratique.com/en/vba/introduction.php

alm kong hnd naman ako gnon ka profecient sa vba pero ung mga next na gnwa ko ay...
1) continuous reading lang.
2a) humanap ng code ng iba (madame ako natutunan sa kanila)
2b) nag-hehelp/nagko-correct ng code ng iba. (feeling debugger ahaha)

 

[suske54]

Re: MS excel formula, programming, analysis, presentation and tutorials.

dead link nah poh///pa up nmn

 

[albert3]

Re: MS excel formula, programming, analysis, presentation and tutorials.

Hello po ulit master,

nagkakaproblema po kasi ako sa code na binigay nyo po na nasa baba. Paano po kaya matatangal yung spaces sa buttom ng notepad(attached) kasi po di ko po ma import yung notepad dahil dun sa spaces. Kailangan ko pa po isaisahin yung notepad i open para matangal yung spaces. Patulong naman po master.

VBA CODES:

Sub savesasnotepad()
'
' savesasnotepad Macro
'

'
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\" & Environ("USERNAME") & "\Desktop\" & ActiveSheet.Name & ".txt", FileFormat:= _
xlTextMSDOS, CreateBackup:=False
ActiveWindow.Close
End Sub

 

[romcel11]

Re: MS excel formula, programming, analysis, presentation and tutorials.

dead link nah poh///pa up nmn

Anong link po ung tntry nyo iopen?

- - - Updated - - -

Hello po ulit master,

nagkakaproblema po kasi ako sa code na binigay nyo po na nasa baba. Paano po kaya matatangal yung spaces sa buttom ng notepad(attached) kasi po di ko po ma import yung notepad dahil dun sa spaces. Kailangan ko pa po isaisahin yung notepad i open para matangal yung spaces. Patulong naman po master.

VBA CODES:

Sub savesasnotepad()
'
' savesasnotepad Macro
'

'
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\" & Environ("USERNAME") & "\Desktop\" & ActiveSheet.Name & ".txt", FileFormat:= _
xlTextMSDOS, CreateBackup:=False
ActiveWindow.Close
End Sub

Tol tingn ko ung whitespaces cguro dhiil s worbook n tntry mo i export

 

[albert3]

Re: MS excel formula, programming, analysis, presentation and tutorials.

Anong link po ung tntry nyo iopen?

- - - Updated - - -



Tol tingn ko ung whitespaces cguro dhiil s worbook n tntry mo i export

Opo Master, is there a way po ba na autodelete nya yung last row na walang value?

 

[romcel11]

Re: MS excel formula, programming, analysis, presentation and tutorials.


boss problema nga yung workbook mo.
nakikita ni excel na meron pang mga value dun sa mga cell sa baba.
remove natin yon

STEP #1: highlight lahat ng cell na hnd kailangan

STEP #2: delete lahat ng cell

Note: Save muna para mg take effect. (para ma refresh[?])

STEP #3: Export

Good luck!