i have a merged cell which runs across a multiple columns (but a single row). i would like to insert a column in the middle of this merged cell, but whenever i click on the column header to insert a column, the entire merged range is selected.
is there a way to change the excel setting to avoid this?
how can i (in VBA) select 1 row (eacht time, it's an ? iteration? (repeating)), while the first 2 cells are merged, like:
a b c d .....
1
2 merged
3 merged
and i need to select row 3 only
and if someone could explain me how i can use the " find" function (on another sheet)
Is there a line of code that I can use at the beginning of a macro to unmerge all cells on an entire sheet named "Test1"?
View 4 Replies View RelatedI am getting Run-time error 1004 "Cannot Change Part of a Merged Cell". I am trying to copy Sheet1 cell E11 (merged cells E11:G11) to Sheet2 cell E11 (merged cells E11:G11).
Sub PartBalance()
With Sheets("PART REQUEST")
If Range("D11").Value - Range("K11").Value 0 Then
[Code]....
I'm trying to iterate through a range of merged cells, but each cell in the merged row is being iterated through and I want it to go from row to row. This what I have, but the subscript goes out of range since is looking at the cells within the merged rows.
View 4 Replies View RelatedI have merges cells starting at "AS18:CC18" and continuing through "AS616:CC617". I want to loop through each row from row 18 to row 617 and perform an action on the cell content (
code to loop through "AS18:CC18" to "AS616:CC617"?
I need to put a part of a text from a column and place it in a new collumn. At this moment I use 2 columns and an extra sheet to solve this.
I have a collumn with a lot of data like:aaa-bc
bbb
abc-ba-bd
aa-bb
bbb-xx-yy
aa
abc-yy
klmn-zz
klmn-yy-ff
What I need is behind every cell a new cell with only the 1st part like:aaa
bbb
abc
aa
bbb
aa
abc
klmn
klmn
What I do now is use a new column with this formula:
[Code].....
And I made a translation table in which the 1st 4 characters are translated to what I want to see. In a 3d column I use
[Code]....
to get the results I need. Since I need to get results like this very often this method is very time consuming.
Is there a formula available I can use with only 1 new column?
I'm trying to add rows in range that has merged cells and unmerged cells combined
The number of rows that is already available before adding is dynamic so can be different each time
Set up is as following
columns A merged, B merged , C merged, D not : free rows , E merged again (merged start up is from 1-6 but as stated should become dynamic each time a row is added in column E
same set up is repeated from row 7-12 , etc....
so I i'm in block A (combo of merged colums and free rows in E ) one row should be added in colum E (E would then be 1-7 and be part of block A) same for the other "blocks".
I'm trying to create an excel spreadsheet to manage personnel and various ongoing projects, what I'm trying to do is create a custom box with a selection of options (5) that will automaticaly fill a selected range of cells with a colour and 'merged' text that was selected from the custom box.
View 7 Replies View RelatedWhen I set a "for each cell in range" to go through the range, and if there are merged cells in this range, this will result an endless loop.
View 9 Replies View RelatedI've recently been making a macro in visual basic that loops through all my excel files and replaces an old company name to a new company name and It's working great, well except for one thing... It always misses one string in the file and it leaves me with 90% of the file corrected. I think that the string might be a part of a merged range so Range.Find is not able to find it. I was suggested to use this code:
[Code] ......
Because that's the code that is generated when using the replace function in MS Excel 2010, but I keep getting syntax errors?
To initialize some cells/ranges, I am copying a given range and pasting it to another given range using the. Copy and .PaseSpecial methods. However, it would seem that both methods actually select the range(s) for the operations, i.e. the given ranges(s) are activated/selected thus changing the focus on the spreadsheet. I would like to perform both operations without actually selecting the given ranges.
View 5 Replies View RelatedI am receiving a 'subscript out of range' error on the lines of code below.
I would note that all variables are declared and all seem meaningful as regards what you would expect at that point.
Below is a snippet from the immediate window which indicates what the values are:
completecashname C:CashDevelopmentMyFolderoutputCASH042706.xls
cashsheetname Formatted Sheet
cashcurrcolumn A
cashfirstrow 2
cashlastrow 876
Also the workbooks are both closed at this point (but it makes no difference)
Set CashCopy = Workbooks(CompleteCashName).Sheets(CashSheetName). _
Range(CashCurrColumn & Cashfirstrow & ":K" & Cashlastrow).Value
Set PelPaste = Workbooks(completepelname).Sheets(PELSheetName). _
Range((PELCurrColumn & PELlastrow)).Value
What i'm trying to do is select a range based on the date which happens to be in the first column of the range.
For example, I have a column (A) that contains dates and column (B) that contains names.
Its easy enough to select the entire range using
Range("A1:B4").Select
I need someting that will analyse column A and conditionally selct the range based on the date in column A (ie. it will only select the rows where column A has todays date).
So in my code I have two set ranges and want to select from one set range to another. The code I have now does not work because I cannot select Range("POBRF:PO"). This is the code.
[CODE]
ARow = Range("A" & Rows.Count).End(xlUp).Row
Set POBRF = Range("A1:A" & ARow)
BRow = Range("I" & Rows.Count).End(xlUp).Row
Set PO = Range("I1:I" & BRow)
Range("POBRF:PO").Select
[CODE/]
I am having trouble selecting a range in my data to copy to another worksheet. I have declared two variables (startcell and endcell), as seen below, as strings:
Cells.Find(What:=startdate, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
startcell = ActiveCell.Address
Cells.Find(What:=enddate, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
endcell = ActiveCell.Address
After this I would like VBA to copy all the variables in a range that is bounded by startcell and endcell. Here is what my code is currently for selecting the range:
Range("A2:" & endcell).Select
Instead of A2, I would like to select startcell, but I cannot figure out how. Currently I can get VBA to recognise endcell. For example, using the formula above I can copy the range from A2 to endcell. However, when I try and use startcell as the beginning of the range (in place of A2) the code does not work.
I have two tables in two different worksheets. In the first table, rows get deleted if the value in column 'S' (the first row to the right of the table) is not '1', and columns get deleted if the value in row '2' (third row above the table) is not '1'. In the second table, the number of columns stays always the same but rows get deleted if the value in column 'N' (first column to the right of the table) is not '1'.
After the rows and columns are deleted the tables get pasted in a ppt presentation.
Now the problem: I am a beginner in VBA and up til now I am only able to define a certain Range, e.g. ("A5:M17"), to be copied and pasted into ppt. But since the tables will sometimes be bigger than other times depending if rows/columns are deleted, I would rather define a dynamic Range. The top left cell of both tabels to be copied is always 'A5' but the bottom right cell changes.
I have to group some data from a pdf format.
I import them into a excel worksheet, but the problem is with selecting them, because the data are really messed up. I tried selecting them with sumproduct with criteria, but the problem is that the formula wants the criteria to be a text that is in only one cell, whereas in my case there I would like the formula to select for example the cell that has the text "Visa34" as well as the cell with "Visa12,FIB3" (i.e. all the cells with "Visa" even thought there might be other words or letters in the same cell...).
I am trying to check whether a cell is part of a named range. I have a 4*2 range named "kompleks" and wants to check whether the selected cell i part of that named range. I've searched google and this forum, but nothing will work. I've tried
If Target.Name = Range("kompleks") Then
If Target.Name = kompleks Then
And also
If Target.Name.Name = Range("kompleks") Then
It is used as a private sub for the worksheet_change.
I'm trying to write VBA that will unhide all the rows in a designated range ("Apples"), except the first and the last.
I'm using this simple code to hide the range: ...
I have below four column range. I need a formula to sumproduct column A and column D, where column B = "n1", column C = "xyz" and until sum of column A reaches first largest value which is less or equal to a variable, say 15. So, the rows would be 1st, 2nd and 5th. And the result - 1,440.
A B C D
2n1xyz110
5n1xyz112
8n2abc112
3n1abc111
6n1xyz110
6n1abc114
3n1xyz114
2n1abc112
3n2xyz114
8n1xyz114
8n1xyz115
4n1abc115
I have worked our an CSE formula below, but it is really massive. Need to have much simplier one.
={SUMPRODUCT(--($C$1:INDEX($C$1:$C$12,MATCH(LARGE(IF(($C$1:$C$12="xyz")*($B$1:$B$12="n1"),$A$1:$A$12),COUNTIFS($C$1:$C$12,"xyz",$B$1:$B$12,"=n1")-SUM(IF(FREQUENCY(IF(MMULT(--(ROW($A$1:$A$12)>=TRANSPOSE(ROW($A$1:$A$12))),--IF(($C$1:$C$12="xyz")*
[Code]....
How can I add decimal part of values in a range
eg: assume my range has 2.27, 3.1, 3.725, 4.1, 4.35
result should be 789 (27+1+725+1+35)
I am trying to select a range of numbers based on an active cell. I then want to sum those numbers and have that total reported to a specific cell.
For example:
Say I have a column with a list of dates (Jan/04 thru July/06). I want the user to be able to click on any given field and have that field plus the 11 fields above it summed and reported. Any help?
I came up with something like
ActiveCell.Resize(12, 1).Select
This only selects the data from the active cell and goes down...I need the opposite. I need it to select the 11 cells above the active cell (plus the active cell) and sum that data.
I have a spreadsheet with Employee details and each employee has a Indicator and a Score.
I'm battling to get Excel to do a lookup on the Indicator, find the applicable Score Range, see if the individual's Score falls within the range and then inserts the relevant % into a cell in a specific row of each employee.
If we did not have more than 2000 doing it manualy might have been an option. Also, the Percentages and Score Ranges in the matrix might change a couple of times....
I am having a few problems selecting a range. What I am trying to do is select a range where the number of columns stay the same, but the rows are variable; ranging from one row upwards. At the end of the rows, there is a blank row. I currently have;
View 2 Replies View RelatedI am trying to select a range of cells. The range I need to select starts in Cell A1. I need to select every row with data in column 1 up to the first row with no data. I then need to expand the range to all columns up the last column with data, including columns with blank cells.
The problem with my code relates to the selection of columns. It is only selecting up to column F. Column G is empty, Column H has data, Columns I-J are empty, Columns K-R have data.
I want to copy a range where the row is variable (i in the Loop I'm doing) but the columns stay the same. What am I doing wrong?
Workbooks("Labor Info").Sheets("Transfer").Range("A" & i & ":E" & i).Select
I was trying to select all of the pictures/objects in a certain range (A294:L1400) in order to move them all at the same time. How do I do this?
View 1 Replies View RelatedHow would I select a range using variables? For example, say I wanted to say
myRange = Sheets(1).Range("A1:A100"),
but the column was variable. How would I do this if the column number was stored in a variable?
I need to select starting with:.
Columns A-O
Rows 1 - ( LR of ColB - Col A is empty )