So far I have this just for testing but I do not know what I would do to have the user select a region and for me to test if it is a valid size for a matrix. (2x2 or 3x3 are the valid sizes).
Code:
Public Function MyInverse() As Variant
Sub Size()
Dim row As Double
Dim col As Double
[Code] ...........
Else:
MsgBox "You have entered an invalid size of the matrix. Please enter a 2x2 or 3x3 matrix."
End If
End Sub
How to turn off or default the warning box message ...
"Data on the Clipboard is not the same size and shape as the selected area. Do you want to paste the data anyway"
I hate having to click yes on this every time I paste something. I do it hundreds of times a day.
My tables is centered and merged because my external date changes sizes each time I paste to the table.
I have looked all over to try and find an "OFF" warning/message button for this. I just want to know how to turn it off or default it to never pop up again!
I have a column full of 10 digit pone numbers. All different numbers and many different area codes (No dashes):
Example:
3305555555
4583651586
4128563254
Is there a way to remove the three leading numbers from all of the numbers at once for the whole column?
How can I print 2 selected area (highlighted area). I know there is a trick to do that. Right now I can only select (highlight) one area to print, but would like to know how to print 2 or more areas at same time
View 7 Replies View RelatedI'm working on a sheet I have to complete and I'm blocked 'cause I'm not able to find the right formula to get the result I need.
I have a sheet that contains 4 columns with the following content (consider the following just as an example. The real sheet contains more that 25,000 rows.
User ID Repository 1 Repository 2 Repository 3
001FG x
001FG x
10GA x
20PK x
20PK x
20PK x
21CC x
4C1D x
Now, the table contains the user id (unique ID) and three columns that stand for the access right the user has for accessing a specific repository.
This means that a User can have more than one occurrence in the sheet because it could have rights to access different repository (i.e. the user 20PK can access all the repository).
What I should be able to do is to transform the table above to a new one with unique user id and the rights for each repository. I would need something like this:
User ID Repository 1 Repository 2 Repository 3
001FG x x
10GA x
20PK x x x
21CC x
4C1D x
How can I do this by using a formula and not a macro?
I am trying to create a matrix in VBA with variable matrix size. The matrix I need will be anywhere from 3 to 9 columns and 15 to 30 rows. I want to set the size as variables and then create the Matrix using these variables, similar to:
rows = 4
columns = 25
Dim XMatrix(rows, columns) As Variant
I'm trying to paste the column that my code found into the blank cell C18, which may change in the future, but I got an error that I need to select the cell to paste the column to - I'm not getting the code right. Here's what I've tried:
For Each Cell In Sheets("Total Sell Dollars").Range("1:1")
If Cell.Value Like "2014*" Then
BlankRow = Sheets("Total Sell Dollars").Range("C" & Rows.Count).End(xlUp)
Cell.EntireColumn.Copy Sheets("Total Sell Dollars").Cells(BlankRow + 1).Select.Paste
Sheets("Total Sell Dollars").Select
End If
Next
When I run this:
Cell.EntireColumn.Copy Sheets("Total Sell Dollars").Cells(BlankRow + 1).Select.Paste
line gets highlighted and it says:
"Run Time error '424': Object Required"
This is the code that I tried when I got the paste error:
Cell.EntireColumn.Copy Sheets("Total Sell Dollars").Cells(BlankRow + 1)
and the error was: Run-time error '1004'
We can't paste because the Copy area and paste area aren't the same size.
Try one of the following:
Click one cell, then paste.
Select a rectangle that's the same size, then paste.
Once I paste the column that excel found, I want to go to the next column and paste the found column into the cell C19 - which may change in the future and so on until all the found columns with 2014 in them are pasted.
I would like to check if selected cells is a union of several ranges or one solid range. I am sure there is a way, but did not manage to find it. How to do it?
View 2 Replies View RelatedI'd like to check whether all the filters in a pivot table is selected or not.
I am currently using the following code:-
Code:
Count = 0
For i = 1 To Worksheets("WW").PivotTables("PivotTable6").PivotFields("Country").PivotItems.Count
[Code]....
where flag is to flag whether all fields are selected in the pivot field or not.
The problem is the above code is very time consuming.
I'm trying to do is add a signature box (for inventory-taking accountability) directly below the pasted area after I paste it. Now, of course, the copied and pasted area could have 5 rows or it could have 15, and when I copy it from the first sheet and paste it into the second it doesn't preserve the name of the range on the second page, so what I really need to do to add the signature box directly below the last row in the pasted area is specify the location in relation to the last row in the pasted selection, but I don't know how to do that. Using ActiveCell always refers to the top-left-most cell in the selected area. So...
If I have an area selected, how do I refer to the bottom-right-most cell in that selection (in the same way that ActiveCell refers to the top-left-most cell)? Or at least the bottom row? Or, even better, the row below the bottom row?
I need to worked out Hours worked in a timesheet. This was the easy part, the hard part is the clause tha HR threw in, which is:
If you have worked and 8 hour day WITH 1 hr lunch then you qualify for overtime.
if you work a 8 hr day and work through your lunch (1 hour) (so equivelant to 9 hrs) you still do NOT qualify for overtime there for Overtime = 0.
This is cause some people work though their lunch to get overtime, but legally they have to have a break so we are not paying overtime for it. I have basically tried in a formula to replicate this but it works with some data and not with all. attached is an example, as you'll see the formula works in some cells, but not others.
I don't think this is possible, but I thought I would ask.
I have buttons on a sheet.
Then I have drawings, that I make, using different shapes.
Instead of selecting all shapes on the sheet, I want to just select the shapes in a range say B17:F28 so I can group them.
I have some code which selected a certain amount of columns depending on
whether they contain values or not. Once I have this range set and I select
it I want to set this as the print area. I am not quite sure how to do
this. for example I tried:
rngUnion.Select
ActiveSheet.PageSetup.PrintArea = ActiveCell. CurrentRegion.Address
I need to display a selected area in an excel sheet in a VB form! I am not sure how to convert the selected area to an image ! The area already contains some images! I am attaching the file for ur refrence!
I need the portion which is within the frame to be converted as an image and get displayed in a Vb form!
I'm trying to set a variable for a range based on the cells selected. For example, I want variable "myrange" to be set to range("A1:D14") if I click on cell A1 and drag it to D14. So far everything I've tried ends up getting the good old "1004 - Application defined or object defined error".
View 4 Replies View RelatedI want to put vertical values to a selected space with horizontal orientation
See the excel file : FILL IN SELECTED DATA.xlsx‎
I want to select some cells (like A2-A20) and have it draw an arrow down the selected cells. Tried a few different things, none of which worked...
View 14 Replies View RelatedI am wondering within the Excel interface, is it possible to auto-fit the size of a comment box to its content?
I have searched the internet and found that a macro as follows could do the job:
Sub FitComments()
Dim c As Comment
For Each c In ActiveSheet.Comments
c.Shape.TextFrame.AutoSize = True
Next c
End Sub
However, this Macro applies to and resize all of the the comment boxes in the active sheet.
I would like to just auto-fit the comment box only to the selected cell, or even better, to multiple selected cells.
I have the following code that allow me to select all of the cells with the color same as the active cell within a selected range:
Sub SelectCellColor()
Dim CellColorFormat As Long
Dim RangeString
[Code]....
I'd like to ask how can I fix the error so that there will not be a limit of the number of colored cells in a selected area?
I have recorded macro.
What I am trying to achieve is ....
When I select cell A2 and press command button Then - Columns C, D, E are unhidden
-Relative cells in selected row ( in this case C2,D2,E2) change font to 12
- When command button is pressed then C,D,E are hidden and font size goes back to 1
similarly if i select A3 same should happen to C3,D3,E3
Currently all is OK but when I press command button it all happens with the entire column C,D,E
Sample book attached.
Code is as follows:
[code]....
Looking for VBA that can change the font size of "•" char(0149) within any string in selected cells.
Also looking to delete the last "•" char(0149) within any string of selected cells.
What would the syntax for these two functions be?
I'm trying to add a textbox at the current position (selected cell) with a set size, fill color, and border color. I found this: http://msdn.microsoft.com/en-us/libr...8(VS.80).aspx:
View 2 Replies View RelatedI have a data chart on 200 people with overlapping membership in 20+ groups, represeted as binary (1=member, 0=notmember), for example: ...
View 9 Replies View RelatedUsing VBA, I wish to work out the inverse matrix of a large matrix (100*100), but keep getting the # Num! Error. I am using the minverse function. I have defined variable as "variant", does this give me the same possiblities in terms of number size as the variable "Double"?
For a table like the one below produced for the sake of example (actual is much much bigger) I want to make it list rows that are true for a certain column for a certain variable in the matrix. So for say water terrain, which types of activity can I do i.e. swimming. Or for Offroad the activites which I can't do i.e. Run and Swim.
ActivityWaterRoadOffroad
Jog nym
Run nyn
Walk nyy
Swim ynn
y=yes
n=no
m=maybe
Using Excel 2003 I am trying to write a macro to set the print area according to the amount of data in a particular range of cells. I find I can include this instruction
View 2 Replies View RelatedBelow there are all the information needed to understand my problem.
M(1 to R,1 to C): matrix with R rows and C columns [element known]
M(i,j): elements of the matrix M in position i(row)-j(column) [element known]n
I am trying to graph the following data in an area chart with line charts superimposed on the area chart. I have a lot of data (and a lot going on) so I'm trying to figure out the best way to show this in excel from a functional standpoing (i can't get this to work in excel!!) to also an asthetic standpoint (dont want it to look terrible or illegible). This is what I'm trying to chart:
1) Weather data (temperature) by region:
So one region, would be: Northwest
I would like the "area" (so a shaded region) to be the min/max of the temperature data for each month.
2) I would like to show the temperature for each year as a line graph on the chart - so you can see if a year falls in or out of the shaded region.
3) I would like to show a company's sales increases across the same months per year as separate line charts. I may choose to just show the biggest outlier year in the end... or to show 2006 (the latest data).
What I am trying to convery with the chart is that the company's sales is or is not tied to weather deviations. I have attached an excel file with the data. I haven't been able to use the area chart or get a two axis chart to work or get it to look even remotely professional.
I have a workbook with multiple sheets that I need to print.
Is there a way to create a fixed print area within which you can mess around with formatting without extending or shrinking the print area?
I have tried adjusting the margins settings so that they are all the same, but this does nothing to keep a fixed print area.
I am using this code to hide or unhide rows of text on another sheet:
VB:
Sub ProcessSheet1ChangeOnCellJ7(ByVal Target As Range)
Dim sAddress As String
Dim sValue As String
'Get the address of the cell that changed without '$' signs
sAddress = Target.Address(False, False)
[Code]....
When the "Not Pursuing" list box option is selected (in cell "J7" or "J8" in Sheet 1) I need to add (or over-write) "Not Pursuing" to the range of cells in column "B" (in the "Tasks" sheet), but only for that particular Goal, meaning a limited range of cells in column "B". If the "Pursuing - Show All Tasks" option is selected for a Goal then these same cells need to be blank so that the appropriate person can enter their name into the cell.
The purpose for adding "Not Pursuing" automatically to these yellow highlighted cells is that it will facilitate filtering of tasks by individual in the "Tasks" sheet..
Again I have tried several times to upload a sample file and am unable to, which I know makes it more difficult to solve. (Is there some common mistake people make? I know it's an allowed format and is very small in file size....)
Code solution can be entered directly beneath:
VB:
If Target.Value = "Not Pursuing" Then
ActiveWorkbook.Sheets("Tasks").Rows("29:29").EntireRow.Hidden = False
ActiveWorkbook.Sheets("Tasks").Rows("30:48").EntireRow.Hidden = True