Posts by Raha

Hi Team,

Could you please advise me how to loop through the files in a sharepoint(Not network drive).

I found the below coding from Chandoo.Org website which works fine only if we give the complete path of the file.
but i want to loop through the files to download from the share point folder.
Kindly advise.

Option Explicit
Sub DownloadXLFileFromURL()
    Dim myURL As String, sFilename As String
    myURL = "[URL]http://img.chandoo.org/hw/max-change-problem.xlsx[/URL]"
    sFilename = Environ("SystemDrive") & Environ("HomePath") & _
            Application.PathSeparator & "Desktop" & Application.PathSeparator & _
            "file.xlsx"
    
    Dim WinHttpReq As Object, oStream As Object
    Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
    WinHttpReq.Open "GET", myURL, False ', "username", "password"
    WinHttpReq.Send
    
    myURL = WinHttpReq.ResponseBody
    If WinHttpReq.Status = 200 Then
        Set oStream = CreateObject("ADODB.Stream")
        oStream.Open
        oStream.Type = 1
        oStream.Write WinHttpReq.ResponseBody
        oStream.SaveToFile sFilename, 2  ' 1 = no overwrite, 2 = overwrite
        oStream.Close
    End If
End Sub

Thanks in advance for the help.

Regards,
Rahaman.

Re: speed up filter, sum and copy paste to other sheet

Hi Nilem,

Thank you very much for attending my post.

Yes its working good,awesome.

Once again thank you very much for the help.

Regards,
Rahaman.

Hi Team,

Below is the code which i am currently using, which helps me to filter checks and sum all the filtered check values and also copy paste filtered rows to other sheet.

but because of huge check numbers(25,000). Every after 2 or 3 thousand lines it will stop for 20 to 25 second.

kindly advise how to make this smooth,faster and run Continuously.

I am unable to upload the excel file because of its huge size but i have uploaded the text file with the same data.

Kindly advise.

Thanks in advance for the help.

Regards,
Rahaman

Sub Sumvalues()
Dim dic As Object
Dim ocell As Range
Dim Rng As Range
Dim Rng1 As Range
Dim countrow As String
Dim xlCalc As XlCalculation
xlCalc = Application.Calculation




     With Application
	.Calculation = xlCalculationManual
	.ScreenUpdating = False
	.EnableEvents = False
	.DisplayAlerts = False
	.Cursor = xlWait
	.EnableCancelKey = xlErrorHandler
     End With




Application.DisplayStatusBar = True




Set dic = CreateObject("Scripting.dictionary")




With dic
    For Each ocell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        If Not .exists(ocell.Value) Then
            xo = .Item(ocell.Value)
        End If
    Next
        R = .keys
End With




Set ocell = Nothing




countrow = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Count
With ActiveSheet




For J = LBound(R) To UBound(R)
    Application.StatusBar = "Please wait still we have " & UBound(R) - J & " line"
    .Range("A:B").AutoFilter 1, R(J)
    Set Rng = .Range("B2:B" & countrow).SpecialCells(xlCellTypeVisible).Rows
    lbnrow = .Range("B" & Rows.Count).End(xlUp).Row
    .Range("C" & lbnrow).Value = Application.WorksheetFunction.Sum(Rng)
    Rng.EntireRow.Copy Sheets(2).Range("A" & Sheets(2).Range("A" & Sheets(2).Rows.Count).End(xlUp).Row + 2)
Next
    .AutoFilterMode = False
    
    With Application
        .Calculation = xlCalc
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
        .Cursor = xlDefault
        .EnableCancelKey = xlInterrupt
    End With




End With
End Sub

Re: compare sheets b/w 1 & 2 & Move (matching) original & duplicate entire rows to sh

Hi Sir,

Thank you very much for attending my thread & helping me.

"Yes" codings are working fine.

Once again thank you very much for the help.

Regards,
Rahaman.

Hi Team,

Could any one please help me to compare sheet 1 & 2 & move original & duplicates rows to sheet3 using vba dictionary.

i have attach the sample workbook wherein i want to match the cell value between sheet1 & sheet2, if match found then move both original & duplicate cells entire rows to sheet3 using vba dictionary.

Thanks in advance for the help.

Regards,
Rahaman

Re: Copy & paste data using Application input method skipping hidden row

Quote from royUK;702644

Is this correct/ You want to copy the filtered data to the raw data sheet? If so then looping and checking for visible rows is totally unnecessary

Hi Sir,Thanks for attending my thread.I want to copy Data from Raw data sheet using Input method & then want to paste the selected data in to filtered sheet using same method by skipping the hidden rows.

Kindly advice.

Regards,Rahaman

Hi Team,

Could you please advice how to copy & paste data using Application input method.

I want to select & paste required amount of data using Input method skipping the hidden rows(only data pasting sheet is filtered)

In sheet where raw data going to be pasted i want to select only single cell, from onward selected data should be pasted by skipping the hidden rows.

I have attached a sample workbook for your reference in which i have used some coding which works fine but i want to learn better way to accomplish.

I hereby request you to kindly advice with better coding.

Thanks in advance for the help.

Regards,
Rahaman.

Re: vba Binary code to read text file & search particular word & amount associated wi

Quote from smuzoen;699561

You don't have a binary file however you can open a text file for Binary Access as snb has done (or Sequential/Random access). As cytop said you either have a binary file or a text file - they are NOT the same thing. There are certain advantages to binary files which you can find with Google. Why you want this opened for binary access I do not know. To do what you want you can open the text file and test each line to see if your name is present then use a regular expression to write out the results. See attached workbook. I have only used a very basic regular expression pattern however again Google is your friend is you want to be more specific wrt currency format.

Code

Option Explicit




Sub ReadTextFile()




    Dim fName As String
    Dim iFileNum As Integer
    Dim buffer As String
    Const USERNAME = "Rahaman"
    
    'Change to location of your text file
    fName = "C:\temp\test.txt"
    If Len(Dir$(fName)) = 0 Then
        MsgBox "File does not exist", vbCritical
        Exit Sub
    End If
    iFileNum = FreeFile()
    Open fName For Input As iFileNum




    Do While Not EOF(iFileNum)
        Line Input #iFileNum, buffer
        If InStr(buffer, USERNAME) > 0 Then
            Call WriteToSheet(buffer, USERNAME)
        End If
    Loop
    Close iFileNum
End Sub




Sub WriteToSheet(data As String, nameStr As String)
 Dim regex As Object, regMatch As Object
     
    Set regex = CreateObject("vbscript.regexp")
    With regex
        .MultiLine = True
        .Global = True
        .IgnoreCase = True
        .Pattern = "\$[ 0-9,.]*"
    End With
     
    Set regMatch = regex.Execute(data)
    If regex.test(data) Then
        Range("A" & Cells(Rows.Count, "A").End(xlUp).Row + 1) = nameStr
        Range("B" & Cells(Rows.Count, "A").End(xlUp).Row) = regMatch(0)
    End If
End Sub

Display More

Hi Sir,

Thank you very much for helping me.

The codes are working fine as i expected.

I understood the things you advised in the post & will refer the link you provided as more suggestions.

Once again thank you for everything.

Regards,
Rahaman.

Hi Team,

Could you please advise how to read a text file in binary & search a particular word in text file and copy that word & amount associated with if found.

I have attached one text file wherein i have used some dummy line.

If name Rahaman found in text file then binary code should copy both name & amount to the excel sheet.

Kindly advise.

Thanks in advance for the help.

Regards,
Rahaman.

Re: Image Control - userform

Hi Sir,

I am little confused.

Could you please advise me what actually this "adding code Tags" mean's.

Sorry if i am not suppose to.

Regards,
Rahaman

Re: Image Control - userform

Say thanks to Forum.

Re: Image Control - userform

Please try this one


If UserForm1.Image1.Picture Is Nothing Then
UserForm1.Image1.Picture = LoadPicture(path)
Else
MsgBox "Photo already uploaded"
End If

Re: Image Control - userform

Try this

If UserForm1.Image1.Picture = vbNull Then

'Do action

UserForm1.Image1.Picture = LoadPicture(Path)

End if

Re: Excel percentage formula withround off

Quote from holycow;697002

CEILING began to fail on your expected results at 2880

Try

=A2/96*100

Hi Sir,

Once again thank you for reviewing my post.

The below three formula's are working fine for me in all the cases including 2880.

=CEILING(A2+(A2*B2),100)
=MROUND(A2+(A2*B2),100)
=A2/96*100

Please advise with which formula should i stick so that it should not create a wrong result.

Thanks for all your support & help.

Regards,
Rahaman.

Re: Excel percentage formula withround off

Quote from Raha;696989

Hi Sir,

Thank you very much for attending my post.

this formul =A2+(A2*B2) is not giving the correct result which i want.

For eg: 96 4% result of formula =A2+(A2*B2) = "99.84"

Tested workbooks has been attached.

Kindly advise.

Once again thank you for attending my post or thread.

Regards,
Rahaman

Display More

Hi Sir,

This formula =CEILING(A2+(A2*B2),100) is giving the correct reuslt for me.

Hope i am not wrong.

Kindly advise.

Regards,
Rahaman

Re: Excel percentage formula withround off

Quote from Ger Plante;696988

=a2+(a2*b2)

I am on an ipad so can't test.

Ger

Hi Sir,

Thank you very much for attending my post.

this formul =A2+(A2*B2) is not giving the correct result which i want.

For eg: 96 4% result of formula =A2+(A2*B2) = "99.84"

Tested workbooks has been attached.

Kindly advise.

Once again thank you for attending my post or thread.

Regards,
Rahaman

Hi Team

Is it possible to add percentage with round off function or formula to get a round figure, if yes then please advise.

Eg: 96.00+4%=100 ( Result expecting )
192.00+4%=200 ( Resultexpecting )
960.00+4%=1,000 ( Result expecting )
1,056.00+4%=1,100 ( Result expecting)
1,440.00+4%=1,500 ( Result expecting)
5,760.00+4%=6,000 ( Result expecting)
9,600.00+4%=10,000 so on

I have attached the excel sheet for your reference.

Kindly advise.

Thanks in advance for the help.

Regards,
Rahaman.

Re: Loop through subfolders in subfolders

Hi Sir,

Its working fine.

Thanks for the help.

Regards,
Rahaman

Hi Team,

Could you please advise how to loop through the subfolders in subfolders

Eg: Main folder-Subfolder-Subfolder

Thanks in advance for the help.

Regards,
Rahaman.

Re: Vlookup array formula with three creteria

Quote from NBVC;689801

Maybe I am not understanding... but D2 and D3 are the same, so why should E2 and E3 be different?

Hi Sir,

I got what actuall you wated to tell.

Now its working for me.

Thanks for the help & Advice.

Regards,
Rahaman.