# Posts by sobi_256

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

• ## Formula working but when i record macro its not working

Code
``````got it thanks, solution

ActiveCell.Formula = _
"=LET(" & Chr(10) & "   val,AH2," & Chr(10) & "   siz,Y:Y," & Chr(10) & "   prod,AA:AA," & Chr(10) & "   qty,AF:AF," & Chr(10) & "   filt,TRANSPOSE(UNIQUE(FILTER(prod,siz=val)))," & Chr(10) & "   sm,SUMIFS(qty,siz,val,prod,filt)," & Chr(10) & "   TEXTJOIN("", "",,FILTER(sm,sm<>0)&"" ""&filt)" & Chr(10) & ")"``````
• ## Formula working but when i record macro its not working

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?

• ## Formula working but when i record macro its not working

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

Code
``=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

Code
``````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))"``````
• ## Looping through workbooks and copy pasting data between dates

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 .

• ## Looping through workbooks and copy pasting data between dates

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.

• ## indirect formula syntax problem

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

• ## indirect formula syntax problem

Hi,

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

thank you for responding

• ## indirect formula syntax problem

hi Carim

please see the attached file

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

• ## indirect formula syntax problem

hi guys.

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

Code
``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

Code
``````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

• ## Different fonts names and size used in header excel

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.

Code
``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

• ## Data consolidation

thankyou so much

• ## Data consolidation

well thats how i get the file

• ## Data consolidation

i have attached an example workbook in my first post

• ## Data consolidation

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

• ## Data correction in columns

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

• ## if file is open save and close that file

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

• ## if file is open save and close that file

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

• ## sumifs work in one cell and shows 0 in other

thank you so much to both of you and answering my queries

• ## sumifs work in one cell and shows 0 in other

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

• ## sumifs work in one cell and shows 0 in other

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

Code
``=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

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