Posts by sobi_256

Thankyou, if i want to change r1c1 notion can i just change it like this
replace RC[-7] with AH2? or the syntaxt will be different?

Hi guys i have the following formula in CELL AO2, manually it works so i recorded the macro, i selected cell AO2 put the formula in and it works

=LET(a,AH2,b,Y:Y,c,AA:AA,d,AF:AF,e,TRANSPOSE(UNIQUE(FILTER(c,b=a))),f,SUMIFS(d,b,a,c,e),TEXTJOIN(", ",,FILTER(f,f<>0)&" "&e))

macro formula, i select ao2 cell and run the macro again , it gives an error, any help is appreciated

ActiveCell.Formula2R1C1 = _
        "=LET(a,RC[-7],b,C[-16],c,C[-14],d,C[-9],e,TRANSPOSE(UNIQUE(FILTER(c,b=a))),f,SUMIFS(d,b,a,c,e),TEXTJOIN("", "",,FILTER(f,f<>0)&"" ""&e))"

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 .

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

Quote from Carim

Hi,

Just to allow for testing ... would you mind attaching a tiny sample file ...

thank you for responding

hi Carim

please see the attached file

in d3 cell the formula is from the macro, calc button

hi guys.

my sheet name is store in D1. if i record a macro i get the right information the formula is as follows

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 out

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

Hi guys

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.

Application.ActiveSheet.PageSetup.RightHeader = "&""Calibri""&45 " & Range("H20").Value

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

Regards

thankyou so much

well thats how i get the file

i have attached an example workbook in my first post

Hi Guys

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

thankyou

regards

example nails.xlsx

Hello guys

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

test1.xlsx

hi it is showing file is open even if file is closed

Hi guys
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

regards

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

Hello

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

=SUMIFS(G:G,A:A,"A",A:A,"G",A:A,"D S")

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

=SUMIFS(G:G,A:A,"<>A",A:A,"<>G",A:A,"<>D S")