Pass Cell Content Into A Filter

Jul 22, 2009

I am doing a simple filter list copy and paste into another worksheet and I have a challenge trying to pass the value in Cell B1 (which contains the city) as a filter parameter in the filtered list.

View 2 Replies


ADVERTISEMENT

Apply Data Filter By Using Content Of Specified Cell?

Dec 10, 2012

I want to use the content of a specific cell in one worksheet to be used to form the filter criteria on a set of data in another worksheet, both sheets are in the same workbook

View 1 Replies View Related

Pass Userform1 Label Content To Label On Userform2

Jun 13, 2013

I have two userforms with a label which displays CompetitorID. I want to transfer content (displayvalue) from UF1.label to UF2.Label. I know labels don't have a value property but want to simply know if it can be done as presently I'm getting run time error 380, can't set property value.

VB : HeadEntryForm.lbCompID = Me.lbCompID ' trf selected competitor ID to ID field on HeadEntryForm

View 3 Replies View Related

Macro - Filter And Copy Associated Content?

Jun 18, 2014

I am having a spot of bother with my spreadsheet, when trying to automate some functionality. Effectively what I am trying to do is...

- With a comprehensive Project Plan press a button that extracts the information of cells that are marked as Critical.

- This information would pull through onto a separate Dashboard sheet, so that those critical items can get flagged to the Project Team.

- The data cannot be copied as a complete table, as there are various columns of data that I do not require copying.

- I have tried recording a macro with me 'filtering' the project plan for critical items and then copying that data across.

- This however only returns the cells originally marked as Critical, it does capture any changes to cells outside of the range in the code.

So,
- In Column C of 'Project Plan' sheet, I have tasks marked as "Critical" or blank.

- I want to copy data of those 'Critical' rows of data, from Columns B,D,F,I

- This data is then to go into the 'Dashboard' sheet, in Columns B,C,E,F.

I embed the code below, from my feeble attempt:

[Code]......

View 12 Replies View Related

Read Cell Content & ADD Content + Font Properties *SOLVE

Sep 12, 2007

I use this to read cell content, add some text/characters (ie. [ and ]) and change the properties of the complete cell

Sub COMMENT()
Worksheets("DVD Lijssie").Activate
If ActiveCell.Value 0 Then ' Change all in to ... ... ...
ActiveCell.FormulaR1C1 = ActiveCell.Value & " " & "]" & " " & "["
With ActiveCell.Font
.Name = "Arial Narrow"
.Size = 8
.ColorIndex = 16
End With
End If
End Sub
HOW can I change this vba-code so it leave's the content of the cell like it is and add some content with the use of let's say TexBox1 and ONLY use different font properties for the newely added content?

View 9 Replies View Related

Change Content Of One Cell Based On Content Of Another

Apr 18, 2014

Formula/macro/etc that would enable me to have content of a cell changed based on the content of another cell in the same row.

Example: cell in column D says "PSA" - so I would need the cell in column H for that same row to read "Radio"

I would need an entire sheet scanned to review for these occurrences and make the appropriate changes. I also would need the formula to be inclusive enough to scan for variations in column D cell content (PSA 1, PSA 2, etc).

View 4 Replies View Related

Matrix - Identify Cell Content In Column For Row If Matrix Content True?

Mar 21, 2014

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

View 10 Replies View Related

Advanced Filter To Filter Cell Comments With The Cell Values

Sep 23, 2006

I have many sheets which I am using the advanced filter to filter the data to a single summary sheet. Everthing works great with one exception. I have cell comments added with relevent info to to cell values. When the advanced filter is performed the cell comments are not transfered to the summary sheet with the cell values. Is there a method to filter the data to another sheet and keep the comments?

View 5 Replies View Related

VBA / Word Picture Content Control Causing Unreadable Content?

Jan 15, 2013

I am using VBA to create a word document (.docx). This word document contains plain text content controls as well as picture content controls. I then use VBA to automatically select a picture based on the code below

Code:
Set oCC = Word.ActiveDocument.SelectContentControlsByTitle("TabPic").Item(1)
On Error GoTo TabErrorHandler
oCC.Range.InlineShapes.AddPicture Filename:="X:XFERANDREW-TDCD " & LblVL &

[Code].....

After the document has been closed down I try to open it again and I am told "The file cannot be opened because there are problems with the contents."

When I click details it says "Unspecified error" and "Location: Part: /word/document.xml, Line: 2, Column: 0"

If I click ok it says "Word found unreadable content in "". Do you want to recover the contents of this document? If you turst the source of this document, click Yes.

Clicking Yes opens the document with all the contents and it is now renamed to Document 1. If I click no it does not open.

View 9 Replies View Related

Pass Cell Value To VBA & Round It Off

Oct 1, 2006

If I have cells that are formatted to a certain decimal width, say, 2 digits, cells that have the value of 3.599 will appear as 3.60. When I reference this cell in VBA, how can I have it pickup the 3.60? Currently, something like Range("A1") or Range("A1").Value will pickup the 3.599.

I feel like theres something thats like Range("A1").XXXX that will get me there, but I can't seem to guess it.

View 6 Replies View Related

Pass Value: Last Used Cell In Column

Oct 30, 2006

I want to use the value of the last cell as a starting value and add a number to it and assign it to the next cell.
i have this

Sub a()
For i = 15 To 100
Cells(i, 2).Value = Cells(i - 1, 2).Value + 6
Next i
End Sub

how do i incorporate this

Sheets("Sheet1").Range("A").End(xlUp).Offset(0,0).value

into the code above so that the value of the last cell can be the starting value.

View 3 Replies View Related

Pass Each Cell Value In Range To Cell & Run Macro Code

Apr 28, 2008

I have workbook template that I use to generate reports from a list of depts. This list is contained in a drop down cell that is a named range in a different worksheet. My current process is as follows:

-Select Dept Name from the list
-Click a command button which is assigned to code that calculates and saves to a file
-Repeat for next report until all reports are generated

I would like to automate this process by producing all reports with a single command with the following functionality:

-The Dept Name needs to be populated in the specified cell containing the current drop down because it drive various vlookups and other formulas
-If possible, I would like to retain the drop down functionality as I would like to have the option of running an individual report or running the “batch”.

View 2 Replies View Related

Merge The Content Of 2 Cells Into 1 (with A Comma Separating The Content)

Jan 29, 2007

how to combine the content of 2 cells into one cell and have the information separated by a comma.

For example:

CELL 1:
Software 1

CELL 2:
Spreadsheet Software

The desired results is:

CELL 3:
Software 1, Spreadsheet Software

View 3 Replies View Related

Input Box To Pass Text To Cell?

Dec 31, 2013

I have the below code which creates a new template

Code:
Private Sub NewTemplate_Click()
Dim Tsh As Worksheet
Set Tsh = Sheets("TEMPLATE")
Tsh.Copy After:=Sheets(Sheets.Count)
shName = InputBox("Please enter new sheet name:")
ActiveSheet.Name = shName
Tsh.Visible = False
End Sub

What I would like to add to the above code is two more inputbox prompts, the first should prompt the user for a Name (text and length) to go into the new sheet cell A6. The the second input box should as for code, (number any length), to go into the new sheet cell b6.

View 3 Replies View Related

Pass Cell Values To VBA Array

Sep 18, 2009

i'm trying to do my homework which requires me to pass values from an array in excel worksheet to VBA and print it in a msg box

i've tried

dim arr as variant
arr = range("A1:A6").value
msgbox arr

but it didn't work.

that's the first step of the homework the second is i have 2 list of array

year:1999 - 2002
profit:10,20,30,40

i have to find the max profit and get the year when it occurs

View 9 Replies View Related

Pass Cell Value Using Variable Row Number

Aug 15, 2008

Following statement works for me:

bdcTerm1 = ThisWorkbook. Sheets("ws2"). Range("A1").Value

But instead I want to parse through 50 rows and dynamically get the value instead of using a static Range("A1"). So I am trying to do the following:


For Row = 1 To bdc_rows
bdcTerm1 = ThisWorkbook.Sheets("ws2").Cell(Row, 1).Value
bdcTerm2 = ThisWorkbook.Sheets("ws2").Cell(Row, 2).Value
Next Row

But I get errror.

View 5 Replies View Related

Pass Cell Variable Between Macros

Jun 11, 2007

I have a macro that nicely select the named range that the active cell is in. I want to chain on to that macro a macro that has a parameter a range with that active selection. I dont see how to "take" the active selection on the worksheet from within the macro and pass it to another. I assume I could change the cellInRange macro to return a range, however I dont yet see how to do that.

View 2 Replies View Related

Pass Cell In Loop To Procedure

Jan 1, 2008

I want to put some colour in the cells B2, B3, B4, B5, etc and a put black the edge of every box. For do that i make a subrutime that put some color in the range that i want (only if i write "B2" or "B2:B5"), but if i want to put a black edge in all the box i can't do "B2:B5". Then i tried to do something like:

For i=2 To 5 Step 1
Call Boxcute(ActiveSheet.Cells(i,2))
Next i

Sub Boxcute (Box As String)
Range(Box).Selection
etc.
End Sub

But Excel gives me a error with Box and Range.

View 2 Replies View Related

Pass Date In Cell To Variable

Jun 3, 2008

I am using this program in one workbook to capture the datevalue in integer from another workbook which i opened. But the program as it reaches the line x2=Datevalue( Cells(2,14).Value) gives a Type Mismatch error.

Public Sub find_date()
Dim x2 As Long

'I am trying to activate the last opened file by using workbooks.count
Workbooks(Workbooks.Count).Activate
Worksheets("Sheet1").Cells(1, 1).Select

x2 = DateValue(Cells(2, 14).Value)

End Sub

Auto Merged Post Until 24 Hrs Passes;btw..the cells(2,14) has a date, formatted in the type of mm/dd/yyyy.

View 4 Replies View Related

Pass Decimal To Variable & Then To Cell

Jun 3, 2008

Why when I want to use a varaible with a value like that 2.1, 0.9, 3.5 in a code to create a formula gives me an error? How to get it work?

Sub Code1()
Dim k As Double
k = 2.1
Range("h11").Formula = "=" & k
End Sub

Strange, but it works well if k is an integer with no Decimal Fraction, like 1, 2, 5, 11 ..

View 4 Replies View Related

Pass WB / WS / Range Info From Cell To VBA Procedure?

May 31, 2013

I need to pass range information (eg. WorkSheets("abc").Range("A1") as text or ??) from a cell (ie the above text is in a cell on some worksheet, say "XYZ") to a VBA procedure or could directly use it in the procedure, similar to...

Sub MyProc(RngInfo as string [or?])
dim RRng as range
Set RRng= RngInfo ??? it is this part I'm really not sure how to do

where RngInfo is a worksheet that has a "named" cell that contains the above cell with the indicated range in it.

eg. RngInfo is worksheets("XYZ").Range("D1") where D1 contains the text (or ?)WorkSheets("abc").Range("A1")

I've only indicated these as text items because I'm not sure what/how you would do this. The end purpose of this is to pass variable Range information from a cell on a WS either into (or use within directly) a VBA Sub. I guess I could pass the WS and Range Address as a single string separated by a "Char" and use Split to separate them and then recombine using Worksheets(Parm(0)).Range(Parm(1)) but it seems like it should be easier than that.

View 9 Replies View Related

Select Cell & Pass Data To Another Object

Dec 22, 2006

I am using excel to stop and start a service on the network and have that part done when using a txtfield to enter in the PC ID. What i want to do is use a list of PCIDs and pass them to my service object to stop and start the service.

Private Sub CommandButton1_Click()

Worksheets("PCIDs"). Range("B2").Select 'my issue is here
Range("B2").Activate 'and here

Do Until IsEmpty(ActiveCell)
Call StopService("ServiceName")
ActiveCell.Offset(1, 0).Select
Loop

Range("B1").Activate
Do Until IsEmpty(ActiveCell)
Call StartService("ServiceName")
ActiveCell.Offset(1, 0).Select
Loop

Worksheets("ServiceName").Select
End Sub

and with this function i need it to pass as a string to txtDeviceID. I have tried just simply setting txtDeviceID as ActiveCell but it didn't like that.

Public Function StopService(ServiceName As String) As Boolean

Dim oSysInfo As New ActiveDs.WinNTSystemInfo
Dim oComp As ActiveDs.IADsComputer
Dim oSvcOp As ActiveDs.IADsServiceOperations
Dim sCompName As String
Dim sSvc As String
Dim lRet As Boolean

View 3 Replies View Related

Change Sheet Name In Cell And Pass Through Formula

Mar 23, 2007

I can't use code for this problem as I need to give this to someone who doesn't know VBA. They will need to use it in several different reports, so I can't produce something in VBA very easily. I have three worksheets, Summary, Year1 and Year2.

I want to display either Year1 or Year2 data in the Summary sheet depending on what the user enters in a cell in the Summary sheet. For example, if the user enters "Year1" in cell E2 then I want to point to a cell in the Year1 spreadsheet.
I tried using formula: =E2 & "!B3" but this doesn't work. Is there another solution? I have attached an example.

View 6 Replies View Related

Find Row Number Of Cell & Pass To Variable

Aug 4, 2007

I am trying to create a macro (please look the attached excel what I have done), I have already crwated a macro that after 5 seconds is gonna change the background color of the of the cell en function of another cell.

That means if F3:F4 change the background color F8:is going to change the backgorung color also, every 5 seconds. The macro I wrote is also include in the excel attached file.

Now this is my problem... I have a variable cell that is give for D6 which could be A,B,C,D,E,F (Range F2:K2).

If the variable cell D6 is A the background color of F8:F9 should be the background color F3:F4 .

If the variable cell D6 is B the background color of F8:F9 should be the background color G3:G4 .

If the variable cell D6 is C the background color of F8:F9 should be the background color H3:H4 ......and etcetera.

View 5 Replies View Related

Pass Date In Cell To Variable & Format

Sep 28, 2007

I'll get straight to the point:

where ( Date > 9/20/2007)

The above Date si used in a sql select statement where I'm hard coding the date (9/20/2007). This date is actually located in sheet1, cell E1. How can I get it from that cell and use it in my Select statement instead of entering the date manually every day in my code?

View 5 Replies View Related

Pass Random Generation In Cell To Variable

Jan 1, 2008

I want to generate a random number based in a poisson and i use this

Application.Run "ATPVBAEN.XLA!Random", ActiveSheet. Range("$B$2"), 1, 1, 5 , , 35

But i want to save the number in a dim single variable called N. I try to put N where i have write ActiveSheet.Range("$B$2") and do N=Application.Run ...
But it doesn't work.

View 5 Replies View Related

Pass A Named Range From A Cell As An Argument To A Formula

Oct 20, 2009

I have the following formula which works fine in this form:

View 4 Replies View Related

Dropdown Listbox In Subroute - How To Pass Cell Argument

Dec 19, 2013

I am coding a spreadsheet that makes extensive use of the excel dropdown list boxes. So I have codes such all over and it is not a neat way to code.

Code:
With Target.Validation
.Delete
.Add xlValidateList, 1, 1, Formula1:="1, 2, 3"
.InCellDropdown = True
.ShowInput = True
End With

As many of the dropdown list boxes are similar in nature, with the only exception that the list content is different, I wanted to code a sub routine to include the code above.

My subroutine looks like this now:

Code:
Sub listbox(cellref As Range)
Set Target = Cells(cellref)
With Target.Validation
.Delete
.Add xlValidateList, 1, 1, Formula1:="1, 2, 3"
.InCellDropdown = True
.ShowInput = True
End With
End Sub

The problem is that when I call the subroutine with a

Call listbox (10,10)

It kept giving me a compile error.

I would like to create a listbox at cell row 10, column 10 of the worksheet.

View 9 Replies View Related

Pass Date From TextBox To Cell & Change Format

Jun 28, 2007

One of my userform text boxes is for the input of a date. this date is being exported to a defined cell in excel, but in a US format, mm/dd/yyyy. i need this to be exported into excel in UK/Aussie format dd/mm/yyyy as our accounting software is getting confused (i am also).


Private Sub TextBox3_Change()
Range("C7").Value = TextBox3.Value
End Sub

View 9 Replies View Related

Pass Row Number From Range Whos Address Is In Cell

Aug 18, 2007

I am setting a variable to the value of an absolute cell reference. I want to set the value of another variable to just the row value of that absolute cell reference.

View 4 Replies View Related







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