If cell.value = {userArray} and cell.offset(0, 2).value = {taskArray}
Then cell.offset(0, 2).interior.colorindex = 3
I have three different {userArray}s and {taskArray}s and there will essentially be different code for each of the arrays and that's fine....
A B C D
--------------------
Mike Fax 5 60
John Tel 3 30
Mike Tel 2 30
Mike Fax 1 60
John Fax 1 60
--------------------
hi all, i am faced with the following problem. before i describe the problem, i do not need help with recording a macro or using functions to solve the above problem, i know how to do that.
writing a code from scratch to do the following:
I need a code that will run through the above columns, and IF A=Mike and B=Fax, THEN C*D...so I want to calculate a total for everytime Mike sells a Fax (or a Tel). Column C is quantity, Column D is price.
I am sure this is a simple loop code, but I have spent days trying to figure it out and I can not do it. Recording a macro is useless, because it is too confusing to look at the code and play with it and try to personalize it.
So I'm creating a grid worksheet for engineering calculations and I have a couple questions about the best way to do it. I've been messing with excel for my calcs for about a decade now, and I every once in a while I try and improve them.
First: I will have several input areas that will either be colored text or shaded background (either works for me). I don't want these 'input required' objects to print as color, just black. But I want my logo at the sheet top to print as color. I've only found ways to not print any color. Can I print the logo as color and the 'input required' stuff as black?
Second: When I do calcs by hand, I write them out on 10x10 grid paper. Each 10x10 grid is one inch. In the past I've created this grid out of the cells, which works. I frequently need to change formulas around though, and each time I do this, I end up needing to mess with the grid cells also. Is there a way to create the grid and have it in the background so it doesn't need to be adjusted each time I change formulas? I wan't the grids to print, and also want to see them on the screen, as I sometimes draw simple objects along with the formulas.
I am retrieving a CSV file from the net. In this file there are 'x' amount of row data and 7 columns. I only care about the values in the 7th column for each row. I also don't care about the entire first row. A graphical version would be represented something like this, with the values I want colored in orange:
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
.
. extending until the end of the data set
.
I've managed to dice this thing into a jagged array by first splitting it using vbLf as a delimiter, and therefore adding those to an array called Lines(). Then I split Lines() up using commas as the delimiter and threw those into a jagged array, let's call it Breadcrumbs()(). I want to throw all the values from Breadcrumbs(i)(6) into an array of its own. Here's my code so far:
Code:
Public Sub CSVparser(file As String)
Dim Lines As Variant
Dim j As Integer
Lines = Split(file, vbLf)
ReDim breadCrumbs(UBound(Lines)) As Variant
For i = 1 to UBound(Lines) - 1
breadCrumbs(i) = Split(Lines(i), ",")
Next i
End Sub
I have three IF statements as below. the problem is if the first statement is true I want it to skip the next two statements or the result will be changed again.
View 2 Replies View RelatedSheet one will contain the following:
DATE A B C D E
2/22/2008TRUEFALSEFALSEFALSERon
2/23/2008FALSETRUETRUETRUEPhill
2/24/2008FALSETRUEFALSEFALSETracy
2/25/2008FALSEFALSEFALSEFALSESharon
2/26/2008TRUETRUEFALSETRUEBill
On sheet two I need to list any date that has three or more true statements with the coresponding name.
EXAMPLE:
2/23/2008Phill
2/26/2008 Bill
How can I combine the two below statements like these to make one "Or Statement"? Sorry...I haven't done excel and/or's in forever?
=IF(('Proj Info'!L10="main")*AND('Proj Info'!L9="CT"),"BMSVC",'Proj Info'!L9)
OR
=IF(('Proj Info'!L10="serv")*AND('Proj Info'!L9="CT"),"BMSVC",'Proj Info'!L9)
I want to use VBA to update a drawing object's color, such as a circle.
For example, pretend I am counting sheep and I need to know when I have reached the maximum count by the visual aide of a stoplight. The script in my head reads: If the max. count of the sheep is less than 5, then the stoplight is green. If the max. count of the sheep is equal to 5, then the stoplight is yellow. If the max. count of the sheep is greater than 5, then the stoplight is red.
Is there a way to do such a thing on excel and with VBA?
I created four Ranges called "Range1", "Range2", "Range3" and "Range4", and wrote short program, that fills them:
Dim i As Integer
For i = 1 To 4
Range("Range" & i).value = 1
Next i
Is it possible to do similiar thing with for example ComboBox?
Let's say that i have comboBoxes named "cb1", "cb2", "cb3" and "cb4" and want to disable them.
I ran into some difficulties by calling an object's function from a collection.
'BondIndex Class Module
Option Explicit
Private m_BondReturn As Collection
Public Sub Initialize()
Set m_BondReturn = New Collection
End Sub
Public Sub Load(BondPoints As Integer)
Dim i As Integer
Initialize
'Load Parameters of Bond
End Sub
Property Get BondReturn() As Collection
Set BondReturn = m_BondReturn
End Property................................
If I hadn't read it on here, how would I have know that they existed? They are not mentioned in the standard Excel help files (not that I could see anyway).
What other "objects" are available? I had a look through the references in the VBE and there are literally hundreds on items which all look pretty "juicy". Does that mean there are a whole host of other objects that I could use in my Excel spreadsheets to either make them simplier or more powerful?
If so, where does one find any reference info on them?
Why should I set objects to nothing (ie clear them) just before ending a sub routine? Doesn't excel dump the variables after the sub is finished running?
Example:
Dim ws As worksheet
Then at the end...
Set ws = Nothing
In the attached sample workbook, (Excel 2007 format), there are hundreds, maybe thousands, of drawing objects and rectangles. They are mostly located near the top of column AC. You can't see any of them, because they are empty, or formatted to have no line and no fill, ubt if you move the cursor around in that area, it will eventually give you the option to "select" one of the objects. They appear to be stacked on top of each other.
This bloats the worksheet terribly. I have managed to shave the size of the attached sample down to 100K, bu deleting about 200 of the objects, but if I save this balnk file as an Excel 2003 file, it is 1.3 Mb.
I have discovered how to show what objects are on the worksheet, by selecting "Find and Select" and choosing to show the "Selection Pane". This pane shows hundreds of blank objects in the sheet. When this pane is open, however, if I try to select and delete an object, Excel locks up on me.
I'm not sure why they decided to export data this way but...I have a report that has pictures/objects that need to be in a cell so I can manipulate the data. I'm not really sure how to do this- and the only thread that related to this was over my head. This is not the original export- I've already done some sorting so the objects are all over the place- but basically if the object is C (picture 23) then I want that picture to say C in a cell somewhere. or if the picture is the dollar sign (picture 27) then I want a cell to say $. I don't know if I'm even explaining this right. Here is my workbook.
View 6 Replies View RelatedIs it possible to change colour of an object (Fill) say fill circle Red when A1="1" and Green when A1="0"
View 12 Replies View RelatedI manually Copied and Pasted contains in TXT File.
The TXT file is as below.
It has only 1 Column
7AB/9AB
8AB/10AB
7CA/10CA
7BB/10BB
7CB/10CB
7AA/9AA
What I want is to run some VB Script so that anything after / is ignored
and the Output shold be reformatted as below
7A1
8A1
7C0
7B1
7C1
7A0
The Above O/p is anything aftyer / is ignore and the last character of the 1st field is A is replace with 0 and B is replace with 1.
Hi all, starting this as a new problem because it's so far different from what I was originally talking about; but this does relate in part to my previous thread.
Anyway. I'm trying to set an array to set Range objects so that I can define each one as a seperate With block. Here's what I "know" when starting out.
I developed the following. I keep thinking it should be multidimensional but my sleep deprived brain came up with this instead. The problem is, it's throwing a 1004 Method "Range of object '_Worksheet'" failed at the With statement.
I have a Master sheet linked with another worksheets with formula.
Now I want to insert 1 column in between , and when trying to insert the column in it a message sowing that " Cannot Shift Objects of Sheet"
Why this error shwoing and how can i insert any column in it.
Here is the Sheet so far: ...
View 9 Replies View RelatedI have a range of cells I want to copy which has some textboxes and check boxes in it. When I do a copy/insert cells in vba it only copies the cells and their values but not any of the boxes.
.Range(.Cells(Origin.Row + 1, Origin.Column), .Cells(Origin.Row + 12, Origin.Column + 10)).EntireRow.Copy
.Range(.Cells(Ind.Row, 3), .Cells(Ind.Row, 3)).EntireRow.Insert Shift:=xlDown
I'm trying to remove gif, jpg, and xls objects from a specific range and I can't seem to figure out how to do it. I can remove all objects from the sheet or only the gif and jpg files from a range. Here is the code I've been playing with:
Sub Clear_Sigs()
Dim Sh As Shape
With Worksheets("Sheet1")
For Each Sh In .Shapes
If Not Application.Intersect(Sh.TopLeftCell, .Range("A26:E32")) Is Nothing Then
If Sh.Type = msoPicture Then Sh.Delete
End If
Next Sh
End With
End Sub
I know this code says to delete only pictures and I've tried tweeking it to do all objects but it doesn't work for just the range I want. Any help is appreciated.
Thanks,
Amy
I want on my chart is an object (Arrow in this case), that is conditional formatted on data between 2 periods.
The first bar of 20 represents a top 20 list with most common failures.
I would like to have an arrow that indicates that if the last period, the amount of failures has gone up, down or is equal to the period before that.
So period 1, article 111 had 10 failures, period 2 article 111 had 20 failures, hence there should be an arrow pointing up related to that bar.
I have a code which i use to grab the highest price and the lowest price from various sheets in column D.
There are 19 sheets altogether and the code basically looks at the relevant sheet based on the value in another cell.
Example - if you type value "EUR/USD" then the highest price and the lowest price are taken from the sheet named "EURUSD".
Now - HOW can i HIDE all those 19 sheets without getting the visible object error?
I have a userform which interfaces with a worksheet as a "front end" and includes a GetData macro that retrieves the relevant data from the worksheet based on the ContractNum value keyed into the ContractNum text box on the form as follows (in part): ...
View 9 Replies View RelatedI have a lot of named objects (Pictures and AutoShapes) on Sheet1 and I need a list of their names in Sheet2!A1.
View 9 Replies View RelatedI have two excel files saved on the network. Test1.xls and Test2.xls. Test1 is the source and test2 is the destination.
When i update a cell in test1, this cell is referenced in test2 - so anybody who opens test2 will see the data i changed in test1.
Here's my problem. I need a way to manually update the cell in test2 without having to close and reopen Excel.
Only opening and closing Excel will ask me if i wish to update the data.
How do i force manual update?
Below is the link in test2 using Excel 2003.
='\servernameshare[test1.xls]Sheet1'!$A$1
Is there code to write that will delete all objects in a range of cells?
I have about 5 buttons at the top of my sheet and I don't want to erase them when I erase objects.
Say a range of D3:AB300
I'm having problems when trying to apply an auto filter to my data. When I select the critieria from the auto-filter drop down, on any field heading, I get this messgae "Fixed Objects Will Move" about 18 times.
View 9 Replies View RelatedI m trying to resize and later, delete a chart object in a macro. I usually record a macro and then look at the code and modify for my needs, but when I resize, I get: ActiveSheet.Shapes("Chart 30").ScaleWidth
and next time I open the spreadsheet the index will be Chart 31 or whatever, so I don't know what the index will be at any given time - how can I get the index of a dynamically created chart? Also, when I wish to delete the chart, there is indexing of the Chart Object as well.
I am trying to work out how to pisition a new object in some VBA code, I can do this if I copy an object then click on a cell, e.g. H74 then paste, but the properties for an object want this in points.
ActiveSheet.Shapes.AddShape(msoShapeOval, 50, 50, 26.25, 26.25).Select
What I want to do is select the cell H74, then find the coordinates for that cell then use these when creating the shape, I appreciate these coordinates will need to be assigned to variables, but how do you get then from a given cell?