Hey good people,
I am trying to protect non-adjacent ranges across multiple worksheets and here's the snipped of the code I have written :
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "topsheet" Then
Set Rng = Application.Union(Range("m:n"), Range("ad:at"))...
Dear @Marc L ,
I revised the code as follows :
Sub consolidate()
'
' pulldata Macro
'
'
Dim asmsheets As Integer 'iterator for looping thru' individual sheets
Dim Topsheetrow As Integer 'select rows for pulling data from asm sheets
Dim Topsheetcol As Integer 'select columns for pulling data...
Hi @Marc L ,
Thanks for the quick revert...
I know somewhere the code is breaking - most probably at the formula string line. But I am no able to write the formula correctly in R1C1 mode. Is there any suggestion on how to rewrite this correctly in the proper syntax and clean this up?
Thanks a...
Hey good people,
I am trying to design a simple algorithm, which should summarize individual worksheets in a workbook into a topsheet.
The code I have tried to write is as follows :
Sub consolidate()
'
' pulldata Macro
'
'
Dim asmsheets As Integer 'iterator for looping thru' individual...
@bosco_yip , I must say this is a very ingenious solution. Had a couple of question as I am working thru' the formula :
1. What does the "1" do after the If function?
2. Can the double dash (--) be used in place of "N"
3. Can the [k] parameter in the AGGREGATE function also be an array by...
Hey, Sumif of course solves the problem for now and so does sumproduct.
I am looking to solve it thru' Index with some future needs in my mind where sumif could become cumbersome to use.
Hey good people...
I am trying to sum a row of values, where the column header matches a given string....there will be multiple matches, precisely, one match every month for the 12 months chosen.
While I have got the result with Sumif and Sumproduct as will be shown in the file, I know there...
I did Vletm. I found nothing relevant to help solve this issue.
My model number is latitude 5300. I also tried with pressing the ESC key to lock/unlock, but again with no success.
It is very irritating as I need to click the "step-into" tab for ever line, while being to use the F8 keys to step...
I use a Dell latitude laptop but cant use the F8 or the Fn+F8 key while trying to debug one step at a time.
The F8 key on my laptop does nothing and the the Fn+F8 key brings up the display options if I have a monitor attached.
Is there some setting on the laptop that needs to be checked?
Any...
I am trying to write a code segment as below
Sub makecols()
'
' makecols Macro
'
'
Dim Rowinsert As Integer 'no. of rows to insert for each channel type count
Dim Myrange As Range 'define the working range
Dim Selchannel As Integer 'set up counter for number of channel types
Dim...
thanks for this Nebu - so you are saying that this cannot work as an array formula as I had written? when I am trying to evaluate the formula, the error seems to be happening when the array checking for condition, returns true and false in the correct way, but when multiplying with the search...
I am trying to pull a column from one sheet to another using the index and match function. But the match function needs to match data across two columns (I know that concatenate and vlookup might solve the problem).
So, I need to pull the data from col. "gm" from sheet3 into sheet2, by matching...
This is a nice solution too....Pivoting would be an obvious choice...but how did you convert the file to the format required for Pivoting? did you use any tool or the query option in microsoft? also how did you convert the column headers, which are in text, to date format?
Thanks
very elegant Bosco - could you help me understand the part where you are following the 2nd match function with +columns....*; I could not follow this part. What is it meant to do? Thanks
Never mind - I figured out what you are doing here with the formula Very simple but impressive. Thanks
I am trying to add the columns in the file attached by quarter by using offset function with dynamic range reference. But unfortunately, I am not able to pass the column references as variable by filtering for year and then the months (e.g. 1-3 would be Q1, 4-6 would be Q2 and so on).
I have...
while trying to solve a issue to merge cells, i came across a segment code as follows :
[rCount = Evaluate("=SUMPRODUCT((--EXACT(""" & cel.Value & """, GSV!$A$6:$A$75)))")
]
while the code seems to work with the above, I am unable to understand the logic and also what the various functions ...
sheet with the formula [check worksheet called pl43...]
https://www.dropbox.com/s/glswpbyp6fkxx8e/consolidated%20adm%20price%20lists%2004sep%202017.xlsx?dl=0
The source sheet to pull data :
https://www.dropbox.com/s/x2vzgiwt29zvwdm/USRO%20and%20other%20dump.xlsx?dl=0
let me know if these...