Offset And End Properties

Feb 14, 2007

I have attach the lab2.xls files below.

1. Download “ Range Data.xls”.

2. Use the Offset and End properties of Range object to name range from A2 to the end of the column as “NEmployees”, range B1 to the end of the row as “NScores”,and the rest of the range, B2 to F19, as “ScoreData”.

3. Do some formatting using the range names and the With-End With construction: make the font of the NEmployees Range in bold and blue color; change the font of the NScores Range to italic, in red and centralize the text (using the HorizontalAlignment property).

View 9 Replies


ADVERTISEMENT

Using OFFSET Function To Return The Value From Another Cell With Row And Column Offset

May 31, 2014

I am having some difficulties using a combination of IF and the OFFSET function to display a range of cell values from another column based on a simple condition. The values I need to display at the destination cells should be offset by 8 columns to the right and "X" rows down from the reference column. The value "X" is to be determined via the IF function to check for the row index number.

For example, if Index value "X" = 8, then display the value of B2 in cell I9. IF X = "9", display B2 in cell I10 etc.

I have attached a sample worksheet that provides some examples.

View 3 Replies View Related

Find Value, Offset & Return Offset To TextBox

Nov 1, 2006

- Find a value on a sheet “ORM” in Column G5:G33

-Get the corresponding value of B5:B33 of that cell

-Place that corresponding value in a canned remark: “You have the number 2 in Block, 12, 14, 25 22.

-Place this canned remark in TextBox31

View 9 Replies View Related

IF And OFFSET: How To OFFSET Only If Cell Has A Value In It

Oct 26, 2008

I have attached the .xls file.

The basic setup is as follows:

(1) the user inputs a number into cells B2:B6 (arbitrarily shown as 3,4,3 and 2)

(2) the user then inputs a series of values into the respective columns E, G, I or K (corresponding to examples A-D, respectively) with SPACING between each value corresponding to the value entered in B2:B6.

For example, the value "4" is chosen in B3, so values are entered in column G at time = 0,4,8,12,16,20....etc

As another example, the value "3" is chosen in B4, so values are entered into column I at time = 8,11,14,17,20,23...etc, ie. the starting point is not necessarily t = 0.

(3) once the values have been entered as described in point (2) above, a series of results are automatically calculated in columns F,H,J and L.

NOTE: I have omitted the formulae for the calculation and have just entered arbitrary (color coded for clarity only-i don't need color coding!) numbers alongside each inputted value for the 4 examples A-D.

OK, so what I want to extract from the table for each example is the paired time AND result values, and then show them in a new table.

I have used an OFFSET formula (originally suggested by "daddylonglegs") to do this, and the results i GET are shown in columns O-V.

HOWEVER, what i WANT is the output as shown in columns Y-AF.

So as you will notice, there are 2 problems:

(1) Example C in columns S/T: value in cell B4 = 3, but the first value entered in column I does not start at a multiple of 3, so no values are returned in columns S/T because the offset function only uses row 6 as a reference point. What i want is the result shown in columns AC/AD.

How do i write a formula which says, "look down a column, find the first cell that has a value in it, and then offset from that cell/row" ?

(2) Example D in columns U/V: since the first input in column K starts a t = 12 (ie, cell K18), then i have 5 rows of empty space in columns U/V. What i want is the result shown in columns AE/AF.

Lastly, i should add that i am not necessarily stuck on using OFFSET function, since i keep reading that it is quite volatile.

View 7 Replies View Related

Set Properties Value

Apr 3, 2008

storedPath = .CustomDocumentProperties("PathCertString").Value

Although the question I'm about to ask is not related to Excel, but related to MS Word, the coding is similar.

The above code I used to set the properties value, but I get an error highlighting 'storedPath'. I speculate MS Word does not recognized this word. Is there another word or code that I can use to set the value in the MS Word document properties?

View 9 Replies View Related

Tab Properties

May 25, 2006

Within my code I have restricted the toolbar options that a user can access (i.e. for Menu Option 'Edit''Tools'):

Set myCmd = CommandBars("Worksheet menu bar").Controls("Edit")
myCmd.Controls("Delete Sheet").Enabled = False

But if the user wishes to delete the sheet, they can select the specific WorkSheet 'Tab' and Right-Click to Insert/Delete/Rename the sheet etc.

How do 'hide' these options within VBA? Or is there a Menu setting that I can be set to Enabled = False?

View 6 Replies View Related

Message Box Properties

Mar 29, 2009

message box properties. i m using this

View 3 Replies View Related

GetOpenFilename Properties

Nov 16, 2008

Is it possible to disable The "Look In:" field of the GetOpenFilename dialogue?
What I would like to do is to keep users from selecting folders other than the CurrentDirectory settings and if possible to keep the user from deleting,copying and pasteing to the files in the current dirrectory displayed. The code I have is:

View 4 Replies View Related

Add Properties Details

Dec 20, 2008

I would like to programatically add information to an Excel file's Properties, the Details tab. I have alot of files in the applicable group. Files are .xls but I'm using Excel 2007.

View 3 Replies View Related

Way To Set The Printer's Properties Using VBA

Oct 16, 2007

Is there a way to set the printer's properties using VBA?

Sometimes we set the printer for BEST quality to do photos or brochures. Well if we don't change it back, when we go to print a spreadsheet, it takes forever because it is set on best quality.

So......
I want to be able to set the print properties to normal using VBA.

View 9 Replies View Related

PivatTable Properties

Jul 26, 2008

I have a problem with the PivatTable properties in Excel and VBA. The problem is as follows:

This is a example table:

Sum of store_sales time_id product_id store_id 367 368 369 1 3 6 7 11,4 11 13 14

(the format is not real clear, but I will explain)

This is a part of a PivotTable where:
Sum of Store_sales is located in the datafield(one record; 11,4)
product_id and store_id are Row-Items
Time_id is a column item.

Now, I want the properties of the cell containing 11,4. I've made it so far in VBA that I can ask what his column-items and his row-item are.

Column = Application.Range(chosenCell).PivotCell.ColumnItems.Item(1)
Row = Application.Range(chosenCell).PivotCell.RowItems.Item(1)
Row2 = Application.Range(chosenCell).PivotCell.RowItems.Item(2)
But how do I get VBA to return the valueheaders of those columns and rows? So actually, I want VBA to also return the names: product_id, store_id and time_id. This is because I need those headers to create a query which I send to a Access database.

Is someone able to give me a hint? Is there a method for this in VBA?

View 9 Replies View Related

Cell Properties

May 16, 2006

where I can find a comprehensive list of '. Cells()' properties that I can Test for/Apply to Excel Cells?

i.e.
Cells(x,y).NumberFormat

I want to set Conditional Formatting using VBA,
to test for:

Data Type (Character, Integer, Date, Decimal, Logical)
Field Length (x(50), 999, 99/99/9999, 999.99, Yes/No)

from an imported file.

View 3 Replies View Related

Properties Of A Worksheet

Sep 22, 2006

I have a workbook with 10 worksheets and I need to know the memory size for each worksheet. I know from File/ Properties that the file is 3.7mb but that is much higher than I would have expected. I can't tell which worksheets are causing it to be so large.

View 5 Replies View Related

VBA Search / Replace With Offset 1 Column And Offset 2 Column

Mar 22, 2013

I am trying to work out how to nest offset in a code using search replace. The goal is to find a value from cell A1 (example) and replace the values in the cells next to the cell containing samuel and the cell containing samuel.

A sort of search and remove data tool if you like So for example:

[QUOTE]A1: "samuel" (the search criteria)
Search range is: B1:D400
(for example)
Cell B40 = samuel
C40 = Driver
D40 = year

So, the macro is activated and finds "samuel" in B40 and I would like C40 & D40 replaced with "" The code I'm using is below: (this is just replacing the cell containing the search criteria with "test". I would like to nest offset(0,2) & offset(0,3).Value = ""

Sub Macro1()
Range("B1:D400").Select
Selection.Replace What:=Sheets("Sheet1").Range("A1").Value, Replacement:="test", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

View 2 Replies View Related

How To Set Class Object Properties

Jul 17, 2014

I have this textbox class which I want to show a userform when clicked and prevent manual input.

[Code] .....

I would expect that I could also set some object properties like color, width, height, locked etc. in the class module.

However I can't find how to (seen all corners of the internet). How do I set these properties?

View 10 Replies View Related

How To Change The Properties Of A CheckBox Using VBA

Aug 4, 2014

I am trying to use VBA to change the caption of checkboxes in "Sheet 2" when I change the value of a cell "A1" in "Sheet 1".

This code is working:

Private Sub Worksheet_Change(ByVal Target As Range)If Intersect(Target, ActiveSheet.Range("A1")) Is Nothing Then Exit Sub

Worksheets("Sheet 2").CheckBox1.Caption = "New Caption"End Sub

But there are 6 checkboxes in Sheet 2 and I would like to do something like this:

Private Sub Worksheet_Change(ByVal Target As Range)If Intersect(Target, ActiveSheet.Range("A1")) Is Nothing Then Exit Sub
For i = 0 to 5Worksheets("Sheet 2").Control("CheckBox" & i+1).Caption = "Box" & i+1Next iEnd Sub

This doesn't work.. So I guess the Control-function is wrong.

View 3 Replies View Related

Set Worksheet Button Properties

Oct 28, 2008

I have a button on a worksheet that activates a macro, the macro is stored in a "personal.xlsb" file. This file is copied to several users computers so they can use the macro, problem is once the button is assigned to a macro from one computer all the other users can't use the macro. What can I do to make this macro work on all computers? (Less placing yet another button on the tool bar).

View 2 Replies View Related

Accessing The Properties Of A Shape

Apr 2, 2009

How does one access the properties of a shape? For instance getting the text on a button ( from the forms toolbar ) on a worksheet. This works

View 4 Replies View Related

Extract File Properties

Mar 1, 2012

I have a folder with some 126 word document files.

What I need is to create list of file names in a column & its properties like Author, Date modified in adjacent columns of each file..

View 1 Replies View Related

VBA Of Current Shape Properties

Mar 31, 2013

I have a series of shapes (circles) that I wish to use as a substitute to the radio buttons found in the form controls (too small).

I've drawn the shapes, but wish to manipulate their properties in VBA. For example, when the user clicks on the shape, the macro includes VBA which will change the properties to include colour and fill. Of course, clicking it again, will send it back to the default.

How can I get the VBA of the current shape's (default) properties? Where can I find a list of the VBA properties available to any particular shape so I know what fields are available to set attributes to?

View 2 Replies View Related

VBA Properties Not Saving After Exit?

Jul 2, 2013

I have 2 sheets to this program. I open the properties to change the ScrollArea of Sheet1 to $A$1:$N$24 so the users that will be using this cannot scroll down to see data. As soon as I hit save, or even Save As and replace the file, then reopen it, I am able to scroll on Sheet1. Now, I do not recall if this same thing was happening prior to restricting access to the workbook or not. One thought I have is because I am the fully authorized user, is that why, even after saving I am able to scroll? Would someone who does not have the permissions that I do in the work book not be able to scroll? Or am I just missing something small?

View 7 Replies View Related

Control Similar To The Properties One In The VBE

Feb 28, 2007

I am after a control similar to the Properties one in the VBE. The one where you modify properties for a control in design mode.

Does anybody know of a suitable Control that I can use in my project that has similar usage?

View 9 Replies View Related

Customized Protection Properties

Mar 26, 2007

I need to have all cells locked and protected, but I need to be able to select all cells in column A only. With 2003 I can chose to be able to select locked cells in the Protection Properties, but that allows at the same time to select all cells and I want a property to allow selection in column A only.

View 9 Replies View Related

VBA Read/write .MP3 & .WMA Properties()

Jun 30, 2007

I have had a long-held belief that Excel/VBA methodology is very suited to solving this problem. I have investigated several possible methods noted below and picked one that gives a "quick win". I offer my results so far as a step towards the goal, as well as giving an example of how to manipulate a non-MS Office application using VBA. In this case Windows Explorer - I have used similar code on corporate applications like Oracle and SAP. My 'Write' method uses Sendkeys. Luckily I have been able to do the job without having to use code to simulate mouse functions or using API calls to simulate key presses. Ideally I would like to use something more stable, but there is the bonus that it is simple, as well as changing WMA and both versions of MP3 tag (see below) if present. Perhaps on reading this someone else may have a better method.

Being a ballroom dancer I have a large collection of CDs as well as software to rip to hard drive and enhance the sound quality. Many are quite old. A big problem has been to get a consistent view of the file properties - especially Genre, (eg. Waltz, Foxtrot .. etc.) which, from my own CD burning or external sources, is missing, or incorrect from using the now obsolete ID3v1 tag standard list. Applications such as Windows Media Player and RealPlayer allow functionality to edit tags but become very tiresome when it comes to making bulk changes - such as after burning a new CD. When viewing properties of the same file in various other applications they often show things like Title & Artist switched, and Genre not at all. I have tried software to change MP3 file tags, but find them over-complicated, confusing, and difficult to make the bulk changes I need. With Windows Explorer we can only change 8 properties - but I find these sufficient.

My method is :-

1. READ : Run one macro to put data into a worksheet.....

2. MAKE CHANGES : Make manual changes to the Excel worksheet in the normal way....

3. WRITE : Run another macro to read the worksheet and update the file properties in Explorer.

Part 1 is very simple and robust. Part 3 is difficult because when we use Sendkeys to mimic keyboard entry the code runs too fast to allow time for things to happen on screen, so we have to put Wait statements *depending on how fast the computer runs*. So Slower is better - up to a point.

Properties are added to MP3 files by using a "Tag" - additional bytes of information which form part of the file. WMA files are a Microsoft invention using a similar, but different structure. Interestingly, using my code to make changes via Window Explorer updates BOTH MP3 Tag versions as well as .WMA files. I moan about Microsoft less and less.

MP3 *ID3v1* consisting of 128 bytes always at the end of the file is now 'obsolete' - despite being still in use. This is very easy to read/write using the same code as for Text Files eg.


Open "c:myfile.mp3" For Binary As #1

etc. It is, however, limited to 4 text fields of 30 characters max, 'Year' =4 characters, and 'Genre' is a single character, the Asc() code of which is a lookup to a standard list which contains 125 items - none of which is any good to me. Could have my own lookup I suppose.

MP3 *ID3v2.3* is in the process of being superseded by ID3v2.4. The big problem here is that there are several different versions and the code required is extremely complicated - mainly due to the use of variable length fields. So we not only have to find the property, but read the field length before getting the field contents. Writing would need to change the coded field length. This is further complicated by there being the option to use an "Extended Tag" - *or not* ! Version 2 tag can be at the beginning or end of the file (before ID3v1 if it exists) - or both. The MP3 files on my computer all seem to have both versions - v2 at the beginning and v1 at the end. It is further complicated by the ability to have User Defined fields. I see some of my files have a user defined 'Genre' field, despite having the standard one 'TCON' too. The tag also needs a form of "encryption" so that the mp3 player does not treat it as audio data. Visit here for detailed information http://www.id3.org .

To view an audio file in its raw state open it in a Text Editor. I use 'TexPad' which is very fast and gives a choice of Binary (with Text 'translation' in a column) or Text view. Notepad gives just a Text view with empty space for non-text/binary characters. There is a large number of Null characters Asc(0) in proportion to the overall file length - mainly for "future development" I believe.

I would be interested to hear of any comments, suggestions and code improvements.

READ CODE IS IN THE "REPLY" BELOW

View 9 Replies View Related

Properties :: Difference In Size

Jul 8, 2007

In the properties in one of my wb's the Size measures to 1,40 MB, but the Size on disk only measure 408 KB. If I make a copy of the same wb, the copy displays 1, 40 on both.

Why do I have a different size on the original?

View 9 Replies View Related

Combobox - Properties - Listfillrange

May 5, 2008

When in a Combobox from the Control toolbox toolbar, the data for the Listfillrange are in a column (e.g.: myrange = $A:$A), it works fine. But when the data in myrange are in a row (eg.: myrange is =$1:$1) , the combobox shows nothing!. What can I do to show the combobox all data (A1,B1,C1,D1 - last data?) ?

View 9 Replies View Related

Mimic The Properties Userform

Aug 1, 2008

Is it possible to create a userform that mimics the Properties userform using VBA by itself. I'm not sure what the form style would be called otherwise I'd have googled it; but it looks pretty cool.

View 9 Replies View Related

Arrays As Object Properties--VBA

Sep 10, 2008

After much online digging, and experimentation, I think I determined earlier that you can't really expose an array all-at-once if it is an object/class property, as you can with an array variable. Now I'd like to confirm this, if I could, because it would be neater to run my simulation in an object instead of a giant and labrynthine user-defined type.

I try the following ....

View 9 Replies View Related

Object Properties, To Display Far Right

May 23, 2006

to locate/display a help file, to a far right position with 80% height and 20% width.

Where do I find the property commands to do this? (with values explanations)
or can someone point me in the right direction.

Current working code, but displays centre...

Private Sub CB_HelpMain_Click()

Set Module1.HELPFILE = CreateObject("InternetExplorer.Application")
Module1.HELPFILE.Navigate "c: empsample_htmlfile.html"
Module1.HELPFILE.Visible = True

End Sub

View 5 Replies View Related

VBA - TreeView Properties In UserForm

Jun 24, 2006

I have a tree view control property in a userform working fine, however, when the form open, only the top level is list (ie, only 1 string is visible). In order to see more, then user needs to click on the + which is not an issue, but they would rather have the tree open up to certain level, say 5 hierarchical levels down, instead of just the one string.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved