hi thanks, i can't really change the layout of the existing files, i understand what u mean though, because if dates are in 1 column i can use the autofilter then .
Posts by sobi_256
Hi guys , need some help with vba.
i am looping through workbooks and copy pasting the data in my macro sheet, then i delete the columns which do not fall in between dates(dates from data sheet in macro file). i am attaching the files, the vba works but if the data is big then it takes too long to complete. i am using copy pasting alot. please advise(novice in vba, just starting), any way i can use some other method , i tried setting range equal to range method but i got an error.
hi Carim thank you for the reply and the answer
can u please highlight these issues, i will try to research on my own, just wanna learn
Just to allow for testing ... would you mind attaching a tiny sample file ...
thank you for responding
please see the attached file
in d3 cell the formula is from the macro, calc button
my sheet name is store in D1. if i record a macro i get the right information the formula is as followsCode
ws.Range("d3").Formula = "=INDEX(INDIRECT(""'""&R1C&""'!""&""1:2""),2,MATCH(RC3,INDIRECT(""'""&R1C&""'!""&""1:1""),0))"
i wanted to use a different way to find it, using variables so i used the following formula which doesnt work , some syntax issue which i am not able to figure outCode
Dim drng As Range Dim ws as worksheet Set ws = Worksheets("Staff") Set drng = ws.Cells(1, 4) ws.Range("d3").Formula = "=INDEX(INDIRECT(""'"" & drng.Address(True, False) & ""'!""&""1:2""),2,MATCH(C3,INDIRECT(""'"" & drng.Address(True, False) & ""'!""&""1:1""),0))"
i also tried to remove 1 set of speech marks from either side of & but i am stuck, any help would be greatly appreciated
i am finding it difficult to set right header using multiple fonts. i am using below code which takes the value from cell and change font type and size.
if i want to add another cell to header, lets say H21 but with a different font, lets say times new roman and size 20 what should i add in the above code.
Any help will be appreciated
thankyou so much
well thats how i get the file
i have attached an example workbook in my first post
from columns E till O i have some data
is thr any way via vba i can consolidate this data in the form as shown in Column Ato C
Sometimes the data in column A get mixed up. i want the data to be sorted out as shown in column B.i only posted in column B so that its easier to explain. what i want is a vba code that can remove the corrupt data in column A and replace the those two values in column A with the values as shown in column B
hi it is showing file is open even if file is closed
i am opening a text file via vba. I am struggling to check if the text file is already open or not, if its open can we save and close that file. if its not open then run some code. i tested the below code , even if file is open it says in the msg box that file is closed, i get the same message if file is closed(which is correct) .any help please
Dim ret ret = IsWorkBookOpen("C:\Users\user\Desktop\delivery.txt") If ret = True Then MsgBox "File is open" Else 'code to save and close the file MsgBox "File is Closed" End If End Sub Function IsWorkBookOpen(FileName As String) Dim ff As Long, ErrNo As Long On Error Resume Next ff = FreeFile() Open FileName For Input Lock Read As #ff Close ff ErrNo = Err On Error GoTo 0 Select Case ErrNo Case 0: IsWorkBookOpen = False Case 70: IsWorkBookOpen = True Case Else: Error ErrNo End Select End Function
thank you so much to both of you and answering my queries
thank you for the reply, just for uderstanding, why the formula works if i use<> , the only difference between the two formulas is that <> is missing
i basically want to sum A,G, and D S from column A, my sum range is in column G, sorry my formulas are wong in the test file. but i still getting the same issue
i did use the below formula in cell k1 and this works, but the above formula does not. i will reupload the file again with the correction in formulas. I just wanna know where i am going wrong because if above formula is wrong then the below one shouldn't work as well
Can someone pls explain why the formula works in k1 but not in k2, if anyone has the answer or a different formula pls lemme know
thankyou all , really appreciate the help
thankyou i think i have got it, i used from your previous code