Most of our features and services are available only to members, so we encourage you to login or register a new account. Registration is free, fast and simple. You only need to provide a valid email. Being a member you'll gain access to all member forums and features, post a message to ask question or provide answer, and share or find resources related to mobile phones, tablets, computers, game consoles, and multimedia.
All that and more, so what are you waiting for, click the register button and join us now! Ito ang website na ginawa ng pinoy para sa pinoy!
Ung unique values ay siya rin yang nakikita mo sir.
W1
W2
W3
C3
C3
etc....
for example sa W1, since 2 ang nakikita mo, isang W1 lang kukunin mo as unique and then sa Table mo it shows na 2 ang quantity niya, parang countif lang. possible ma?
di ko naintindihan
post ka ng worksheet or kahit yung screenshot ng gusto mo maging hitsura ng output.
pivot ata need nya. parang magmerge lahat as one. di uulit.
Without using pivotable daw eh
Set BitMar = CreateObject("WinHttp.WinHttpRequest.5.1")
BitMar.Open "GET", "https://api.binance.com/api/v1/ticker/24hr"
BitMar.send
Dim Json As Dictionary
Set Json = JsonConverter.ParseJson(BitMar.responseText)
''Get the result
Dim Cur As Variant
ReDim Cur(Json("result").Count, 21)
Dim Pum As Variant
ReDim Pum(Json("result").Count, 21)
Dim Vol As Variant
ReDim Vol(Json("result").Count, 21)
Dim Cu As Dictionary
Dim i As Long
Dim j As Long
Dim k As Long
i = 0
j = 0
k = 0
For Each Cu In Json("result")
''Get the result of specific array
Cur(i, 0) = Cu("symbol")
Cur(i, 1) = Cu("lastPrice")
Cur(i, 2) = Cu("quoteVolume")
Cur(i, 3) = Cu("bidPrice")
Cur(i, 4) = Cu("askPrice")
Cur(i, 5) = Cu("prevClosePrice")
i = i + 1
sir, paturo na naman sa graph & control mo. gusto ko matuto niyan pano mo ginwa yan?
Need Help mga Sir,
Formula Needed:
Column Value
W01 5
W02 20
W01 100
W03 10
W01 3
Please need ko help formula for Total Value of W01......Salamat sa maksagot.....
Column Value
1 W01 5
2 W02 20
3 W01 100
4 W03 10
5 W01 3
6 =SUMIFS(C:C,B:B,"W01")
Hello,
I need some help in parsing the JSON from a certain site.
When Json used as Dictionary, the error is: Run-time error '13': Type mismatch
When Json used as Object, the error is: Invalid procedure call or argument
Code:Set BitMar = CreateObject("WinHttp.WinHttpRequest.5.1") BitMar.Open "GET", "https://api.binance.com/api/v1/ticker/24hr" BitMar.send Dim Json As Dictionary Set Json = JsonConverter.ParseJson(BitMar.responseText) ''Get the result Dim Cur As Variant ReDim Cur(Json("result").Count, 21) Dim Pum As Variant ReDim Pum(Json("result").Count, 21) Dim Vol As Variant ReDim Vol(Json("result").Count, 21) Dim Cu As Dictionary Dim i As Long Dim j As Long Dim k As Long i = 0 j = 0 k = 0 For Each Cu In Json("result") ''Get the result of specific array Cur(i, 0) = Cu("symbol") Cur(i, 1) = Cu("lastPrice") Cur(i, 2) = Cu("quoteVolume") Cur(i, 3) = Cu("bidPrice") Cur(i, 4) = Cu("askPrice") Cur(i, 5) = Cu("prevClosePrice") i = i + 1
idol sorry tlga, d ko magets. sry din sobrang late reply. ahahahaahaUng unique values ay siya rin yang nakikita mo sir.
W1
W2
W3
C3
C3
etc....
for example sa W1, since 2 ang nakikita mo, isang W1 lang kukunin mo as unique and then sa Table mo it shows na 2 ang quantity niya, parang countif lang. possible ma?
note: Copy paste sample table sa cell "a1"Code:column value 1 w01 5 2 w02 20 3 w01 100 4 w03 10 5 w01 3 6 =sumifs(c:c,b:b,"w01")
syntax:
=sumifs([range na my mga value na i-a-add],[range ng criteria],[anong word/no ang i-a-add lang])
hope this helps ka-symb. D ako marunong mgjson eh
link 1
link 2
link 3
- - - updated - - -
idol sorry tlga, d ko magets. Sry din sobrang late reply. Ahahahaaha
View attachment 1248453
pahelp naman po, paano ko pa ba gagawin yun,
kung wari may list ng mga employees tapos, nakalagay sa mga corresponding column nila yung ilang beses sila umabsent at pumasok, tapos sa pinaka dulong row lalabas yung result kung nakumpleto ba nila yung attendance by displaying perfect or incomplete, please help
Mga masters, QUestion po..
I have the attached excel, may add and search na sya..
Gusto ko sana dagdagan ng workflow sa baba..
If existing, reference number type and "add record" button clicked.
1. Error message for existing Reference number
--- msgbox(Update/Cancel)
--cancel - Clear
--Update - Update, "Status" "Date of completion", Append-new Description of Issue to existing description.
Sub Export()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Addrow = 1
Addcol = 1
Do Until Worksheets("IT Issues Log DB").Cells(Addrow, Addcol) = "" And Worksheets("IT Issues Log DB").Cells(Addrow, Addcol + 1) = "" And Worksheets("IT Issues Log DB").Cells(Addrow, Addcol + 2) = ""
Addrow = Addrow + 1
Loop
If Worksheets("IT Issues Log Form").Range("B2") = "" _
Or Worksheets("IT Issues Log Form").Range("B3") = "" _
Or Worksheets("IT Issues Log Form").Range("E2") = "" _
Or Worksheets("IT Issues Log Form").Range("G2") = "" _
Or Worksheets("IT Issues Log Form").Range("G3") = "" _
Or Worksheets("IT Issues Log Form").Range("B5") = "" _
Or Worksheets("IT Issues Log Form").Range("B10") = "" Then 'Checkpoint
Response2 = MsgBox("Please fill-out all required field", vbOKOnly)
End '<~ !!!
ElseIf isRefExisting(Sheet3.Range("B2"), Sheet7.Range("A:A")) Then '<~ tinawag natin ung function na "isrefexisting"
' yun na ang bahalang magtrabaho para saten :)
MsgBox "Request denied. Reference number already exists"
Exit Sub '<~ hindi po ako gumamit nung "end", in case na itong sub na ito
' ay tinawag ng iba pang sub, prematurely ma teterminate ung flow
' so to be safe exit lang tayo sa sub na to. para continue ung
' sub na tumwag dito. (sana maayos kong naiplwnag)
Else
Call Details
End If
Call ClearSheet
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Function isRefExisting(ByVal strWhat As String, ByVal rWhere As Range) As Boolean
'<~ purpose : return true/false if the strwhat is already existing in range rwhere
' : we handled this through loooping on a 2D array
Dim varArrHolder() As Variant
Dim lLastRow As Long
Dim lLoop As Long
'<~ initialize function
varArrHolder() = resizeRange(rWhere) '2D array
lLastRow = getLastRow(rWhere)
For lLoop = 1 To lLastRow
If Trim(UCase(strWhat)) = Trim(UCase(varArrHolder(lLoop, 1))) Then
' what to do if what we are looking for is already existing?
' we terminate the function and return true
isRefExisting = True
Exit Function
End If
Next
End Function
Function getLastRow(ByVal rWhat As Range) As Long
'<~ purpose : gets last row of a given range
' : returns it as long
getLastRow = rWhat.Cells(rWhat.Cells.Count, 1).End(xlUp).Row
End Function
Function resizeRange(ByVal rWhat As Range, Optional ByVal lCols As Long = 1) As Range
'<~ purpose : returns a resized range based on its lastrow
' : lcols is optional but you can set it to force
' resizing of cols.
If lCols < 1 Then Exit Function
Set resizeRange = rWhat.Resize(getLastRow(rWhat), lCols)
End Function
Worksheets("IT Issues Log Form").Range("B2")
isRefExisting(Sheet3.Range("B2"), Sheet7.Range("A:A"))
copy paste lang dun sa module. tapos delete mo muna ung "Export" sub mo.Code:Sub Export() Application.ScreenUpdating = False Application.DisplayAlerts = False Addrow = 1 Addcol = 1 Do Until Worksheets("IT Issues Log DB").Cells(Addrow, Addcol) = "" And Worksheets("IT Issues Log DB").Cells(Addrow, Addcol + 1) = "" And Worksheets("IT Issues Log DB").Cells(Addrow, Addcol + 2) = "" Addrow = Addrow + 1 Loop If Worksheets("IT Issues Log Form").Range("B2") = "" _ Or Worksheets("IT Issues Log Form").Range("B3") = "" _ Or Worksheets("IT Issues Log Form").Range("E2") = "" _ Or Worksheets("IT Issues Log Form").Range("G2") = "" _ Or Worksheets("IT Issues Log Form").Range("G3") = "" _ Or Worksheets("IT Issues Log Form").Range("B5") = "" _ Or Worksheets("IT Issues Log Form").Range("B10") = "" Then 'Checkpoint Response2 = MsgBox("Please fill-out all required field", vbOKOnly) End '<~ !!! ElseIf isRefExisting(Sheet3.Range("B2"), Sheet7.Range("A:A")) Then '<~ tinawag natin ung function na "isrefexisting" ' yun na ang bahalang magtrabaho para saten :) MsgBox "Request denied. Reference number already exists" Exit Sub '<~ hindi po ako gumamit nung "end", in case na itong sub na ito ' ay tinawag ng iba pang sub, prematurely ma teterminate ung flow ' so to be safe exit lang tayo sa sub na to. para continue ung ' sub na tumwag dito. (sana maayos kong naiplwnag) Else Call Details End If Call ClearSheet Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub Function isRefExisting(ByVal strWhat As String, ByVal rWhere As Range) As Boolean '<~ purpose : return true/false if the strwhat is already existing in range rwhere ' : we handled this through loooping on a 2D array Dim varArrHolder() As Variant Dim lLastRow As Long Dim lLoop As Long '<~ initialize function varArrHolder() = resizeRange(rWhere) '2D array lLastRow = getLastRow(rWhere) For lLoop = 1 To lLastRow If Trim(UCase(strWhat)) = Trim(UCase(varArrHolder(lLoop, 1))) Then ' what to do if what we are looking for is already existing? ' we terminate the function and return true isRefExisting = True Exit Function End If Next End Function Function getLastRow(ByVal rWhat As Range) As Long '<~ purpose : gets last row of a given range ' : returns it as long getLastRow = rWhat.Cells(rWhat.Cells.Count, 1).End(xlUp).Row End Function Function resizeRange(ByVal rWhat As Range, Optional ByVal lCols As Long = 1) As Range '<~ purpose : returns a resized range based on its lastrow ' : lcols is optional but you can set it to force ' resizing of cols. If lCols < 1 Then Exit Function Set resizeRange = rWhat.Resize(getLastRow(rWhat), lCols) End Function
-- update --
additional lang din. ung pag-refer mo po sa isang worksheet withprone to sa error, kpag napaltan ung pangalan nung worksheet.Code:Worksheets("IT Issues Log Form").Range("B2")
suggestion, mag refer sa worksheet using ung codename nila likeCode:isRefExisting(Sheet3.Range("B2"), Sheet7.Range("A:A"))
para naman makita ung codename ng isang sheet. refer to image below
View attachment 1249344
*isang function pa lang ung nadadagdag.
medyo madaming babaguhin sa codes mo.