Over 1 million tech questions and answers.

Solved: Set cell selection based on a variable offset?

Q: Solved: Set cell selection based on a variable offset?

I know that this is probably a pretty trivial problem, but I can't seem to come up with the answer. I am trying to set the selection focus to a cell in a column, based on an offset variable. The following is a little macro that I created to experiment with. As written, it positions the selection focus on cell C6. But I can't find the way to set it to C6 + Offset. The only reason for the MsgBox statement is to prove that I passed the argument as expected.

Sub MvTo(ByVal Offset As Long)

Range("C6").Select
MsgBox "Offset " & Offset

End Sub

I thought I could use RnCn cell references and write something like Range("R6[+Offset]C3").Select, but I get an error return, so I'm missing something somewhere. Run-time error '1004', Method 'Range' of object "_Global' failed.

I am using Excel 2000.

RELEVANCY SCORE 200
Preferred Solution: Solved: Set cell selection based on a variable offset?

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Solved: Set cell selection based on a variable offset?

Read other 6 answers
RELEVANCY SCORE 91.2

i have 2 workbooks entitled [price] and [fruit] respectively

[price], sheet1!, cell A1 is a variable which can contains a cell address, eg $D$1

[fruit], sheet1!, contains information in cells as follows:
D1 = apples
D2 = bananas
D3 = cherries

in the [price] workbook, i want to have a formula in say, cell B1, which would use the cell reference in A1 to find the value in the [fruit] workbook.

so, if [price]sheet1!A1 = $D$1

then [price]sheet1!B1 = apples

note that the result "apples" was pulled from another workbook, [fruit]sheet1!$D$1.

if [price]sheet1!A1 = $D$2,
then B2 = bananas

and so on...
in the [price] workbook,

As A1 is a variable, i do not want to physically retype the value within A1, but rather draw reference to it inside of a formula. i hope this is clear as mud.

Is this possible?

Thanks,
markus
 

A:Q: draw value from a 2nd workbook, based on a variable cell ref in 1st wb

In Sheet1!B1 (of Price), use

=INDIRECT("[Fruit.xls]Sheet1!"&A1)

, where A1 contains the cell reference -- $D$1, $D$2, $D$whatever.

As explained yesterday, this (the INDIRECT function) will only work when both files are open ; to get it to work when the source file if closed, you need to download Laurent's add-in. Please do not start threads over.

Rgds,
Andy
 

Read other 1 answers
RELEVANCY SCORE 78.4

Hi

I am trying to get a date value from one sheet to populate another sheet using the offset function.

Using:

Range(Selection, Selection.End(xlDown)).Offset(0, 3)= " (need to show date here from another sheet) "

Any help would be much appreciated..

Thanks
 

A:Solved: Using Offset in Excel VBA to Return date frm another cell

Read other 8 answers
RELEVANCY SCORE 76.4

Hello,

I am attempting to clear 2 cells, based off the value of another. I am pretty sure the code is correct, because it works within another Macro. Thanks.
Code:
Sub REMOVE()


Dim p As Long
p = Cells(Rows.Count, "a").End(xlUp).Row


For i = 1 To p
Range("k2").Select
If InStr(1, Range("k" & p), "None") > 0 Then Range("L" & p) = "" And Range("M" & p) = ""
'If no Issue, Location/Obsevations should be blank
Next i


End Sub


 

A:Solved: Clearing Cell Contents Based off other Cell

hi
try this variation;
Sub REMOVE()

Dim p As Long
p = Cells(Rows.Count, "a").End(xlUp).Row

For i = 1 To p
If InStr(1, Range("k" & i), "None") > 0 Then
Range("L" & i) = ""
Range("M" & i) = ""
End If
'If no Issue, Location/Obsevations should be blank
Next i

End Sub
 

Read other 2 answers
RELEVANCY SCORE 76

Hi, I couldnt find an answer to my problem from google searching.

Basically I want to transfer data from one workbook to another using the find functions of find, copy, switch workbooks, find, offset, paste, which will run on a couple of hundred names. But I want the offset to change depending on a value I put into one cell in the sheet, i.e. if B2=2 it offsets by 2 columns, if it =3 it offsets by three columns etc, is this possible? I tried putting offset.(FN2,0) and assigning FN2 to equal B2 but it failed.

And pointers would be welcome, thanks.
 

A:Excel Variable offset?

Read other 7 answers
RELEVANCY SCORE 72.8

Hi everyone, this is my first post in the forum. I was wondering if anyone could help me send a variable to the cell that I select(vba).

On my worksheet I have a button that when clicked, stores a value from the cell beneath it
as "a" .I used this code
Code:
a= Range("b2").value
I have another button that will take the value in the variable and send it to a cell on the worksheet.
Code:
Range("[I]this is where I need help[/I] ").value= a
I'd like to send the value in the variable to the cell that I select before hitting the send button.

Thanks in advance for any help.
 

A:Solved: Send a variable to selected cell

Read other 7 answers
RELEVANCY SCORE 70.4

Hello! I have a macro designed to add text before and after the text in a cell. I designed it for formulas, so for example:

Current text: =Vlookup(A1,B1:B44,1,false)
Enter String 1: =iferror(
Enter string 2: ,"")
Final result: =iferror(Vlookup(A1,B1:B44,1,false),"")

I want that formula to apply to every cell within the selection I choose.

The macro I have right now does not move from cell to next cell. Instead, it will repeat the operation on the start cell as many times as you have cells selected. I'm a beginner, so I get a little lost past loops, if statements, and format changes. I compiled this from various macros posted online, but nothing seems to work. Thanks!

Sub ConcatWith2Strings()
Dim X As String
Dim CS As Range
Set CS = Selection
Dim String1 As String
Dim String2 As String
String1 = InputBox("Enter the first string to be entered before text currently in box (include = signs).")
String2 = InputBox("Enter the second string to be entered after text currently in box.")

For Each CS In Selection
If Left(ActiveCell.Formula, 1) = "=" Then ActiveCell.Formula = Right(ActiveCell.Formula, Len(ActiveCell.Formula) - 1)
ActiveCell.Formula = String1 & ActiveCell.Formula & String2
Next
End Sub
 

A:Solved: Apply to each cell in selection

Hi,

Not sure if your code is working, but you need to loop thru the cells.
Something along the follow code.
Code:

For Each CS In Range("A1:A100")
If Left(cells(CS.row,1), 1) = "=" Then cells(CS.Row,1).Formula = Right(cells(CS.row,1).Formula, Len(Cells(CS.row,1).Formula) - 1)
cells(CS.row,1).Formula = String1 & Cells(CS.row,1).Formula & String2
Next CS
 

Read other 3 answers
RELEVANCY SCORE 69.2

Current design displays combo box w/ sort by ADP Company and Location Number. I have two combo boxes. The first on list all the ADP Companies located in table, the second list Location Numbers depending on which ADP Company is selected from the first combo box.

I am trying to get a couple text boxes to populate based on what has been selected using the two combo boxes located on the screen. I am running into trouble with a DLookup function and Im not exactly sure why. Here is my DLookup function:
=DLookUp("[BranchNumber]","[tblAllADPCoCodes]","[ADPCompany]= '" & [cboADPCompany] & "' And [LocationNumber]= '" & [cboLocationNo] & "'")
ADP Company combo box
Then
Location Number [with in ADP Company] combo box
Then
List Branch Number [based on selected ADP Company & Location Number] text box
Then
List Description [based on selected ADP Company & Location Number] text box
 

A:Solved: Populating Text box based on combo box selection

The combo boxes also need to have the fields included in the select statement that you want to populate each textbox. Set the number of columns to the number of columns in the query and set the column widths for the fields you do not want to show to zero. Set the after update event for the combo to something like Me.TextBox.Value = Me.Combo1.Column(Index# of column u want)
 

Read other 1 answers
RELEVANCY SCORE 68.4

Hi everyone,

I'm using Access 2007 and I want to:

Populate a List Box with values based on the selection in a Combo Box
Then I want to be able to select a value from the List Box to find a record on a form.

The scenario here is that Jobs have multiple Items and I want to be able to see the details of an individual item by first selecting a Job then selecting the item from a list of all items in the selected job.

I'm fairly new to this and this is a big jump in complexity for me, I'm looking forward to hearing your responses.

Thanks!
 

A:Solved: Selectable List Box Values Based on Combo Box Selection

Read other 13 answers
RELEVANCY SCORE 68.4

I have a sheet set up with the list with the description (text) in column B, and summary scores (numerical, percentage) in column D. I want to do a summary row at the top of the sheet that pulls the data from the B cells, based on the lowest 3 values in column D.
 
I plan on using the formula =SMALL(D7:D32,1) (with d7:d32 being the list of percentages), to figure out the lowest 3 values. But the formula just pulls the summary score, not the description. I want to pull the description into but I am at a loss.
 
I am using excel 2013 on windows 10. Any help would be appreciated.

Read other answers
RELEVANCY SCORE 68.4

I have a sheet set up with the list with the description (text) in column B, and summary scores (numerical, percentage) in column D. I want to do a summary row at the top of the sheet that pulls the data from the B cells, based on the lowest 3 values in column D.
 
I plan on using the formula =SMALL(D7:D32,1) (with d7:d32 being the list of percentages), to figure out the lowest 3 values. But the formula just pulls the summary score, not the description. I want to pull the description into but I am at a loss.
 
I am using excel 2013 on windows 10. Any help would be appreciated.

Read other answers
RELEVANCY SCORE 68.4

Hello,

I cant seem work out a solution for what I'm trying to do. I have an Excel workbook that has multiple sheets. On sheet 1 i want the data from cell "G3" to be copied onto sheet 2. But i want the location on sheet 2 to be based on whatever was entered into cell "D3" on sheet 1.

For example: Sheet 1, cell D3 I have the name John, in cell G3 i have 68. I want "68" to be pasted in sheet 2 in cell B26.

But if the name in Sheet 1 cell D3 is Suzie, then I want G3 to be pasted in Sheet 2 in cell D26. So I would need to identify the paste location for each person.

I want the data to paste to the next cell so that the next entry can be pasted below the last entry for that person (for John the first entry would go into cell B26, then the next entry would go into cell B27 and so on).

But i want it to be a specific range, i dont want data to be pasted past 20 cells (cell B45). If possible a message box could be created to let the user know that the max is reached.

I would appreciate anyone's help with this as i have been struggling for awhile to try to get this. Thank you
 

A:Excel - Copy paste cell into range based on another cell

Read other 12 answers
RELEVANCY SCORE 67.6

I have a Form called "EngineerSelection" that has a combo box "EngineerSelect" where a person selects an Engineer's name. The person then clicks a button next to the name. OnClick is suppose to open a form called "ProjectFileCoverSheet" and only display the forms where the combo box value at the time of the button being clicked matches the "ProjectEngineer" field on the new form.

I used the following code on the button:

Private Sub Command38_Click()
DoCmd.OpenForm ProjectFileCoverSheet, , , "[ProjectEngineer]='" & Me![EngineerSelect] & "'"
End Sub

However, when I click the button I get a Run-time error '2494': The action or method requires a Form Name argument. The form name is ProjectFileCoverSheet so I am not sure what I am doing wrong? Any help is greatly appreciated.

Danny
 

A:Solved: Access 2007- Open Form based on Combo Box Selection

guess I forgot the quotation marks around the file name:
DoCmd.OpenForm "ProjectFileCoverSheet", , , "[ProjectEngineer]='" & Me![EngineerSelect] & "'"

However, it opens the form but goes to a blank form instead of opening with the information with the matching ProjectEngineer name.
 

Read other 1 answers
RELEVANCY SCORE 67.6

Hi guys,

Can you please help me further expand the macro below? I need it to also look between the ranges of C24 and Z24 and hide the respective column. (i.e need some sort of loop)

i.e

if C24 is 0, then hide column C
if D24 is 0, then hide column D
etc...
Sub Hide_Column ()

If Range("H24").Value = 0 Then
Columns("H").EntireColumn.Hidden = True
Else
Columns("H").EntireColumn.Hidden = False
End If

End Sub
 

A:Solved: Hide Columns based on value of cell

Read other 9 answers
RELEVANCY SCORE 67.6

Does anyone know a way to manipulate the countif formula to count how many cells are "green" or "red" (with varying text in each cell), rather than the value of the cells?

Can this be done in VB?
 

A:Solved: countif based on cell format, not value?

Read other 10 answers
RELEVANCY SCORE 67.6

Hey all:

I'm have really no experience with coding, so I'm out of my depth trying to do this myself. Hopefully someone can help, so here goes!

Essentially, my excel sheet keeps track of dates of when files need to be renewed. They need to be renewed every 60 days. So I have an "IF" formula that tells me if it's time for renewal.

What do I need to do to get an email notification whenever a file comes up for renewal? I've attached a sample sheet.

Best,
Nick

PS.
Using Excel/Outlook 2010
ClickYes Pro is installed (don't know if it will be necessary or not)
 

A:Solved: Emailing from Excel Based off Cell Value

Read other 16 answers
RELEVANCY SCORE 66.8

Hi All,

I've got a query that's based on the value of a combo box on a form, Criteria:

[Forms]![frmSearchTenants]![cmbTenantSurname]Click to expand...

How do I adapt this to say if the cmbTenantSurname IsNull Then Show All, I'm guessing it's "*", but not sure how to write it.

Many thanks,
MRdNk
 

A:Solved: Access : Query based on ComboBox Selection, if combo box is null show all

Update:
I've gone for a new tack, I've programmatically run the query via a module, however now the requery doesn't update the subform.

ComboBox:
Private Sub cmbTenantSurname_AfterUpdate()
Call basDBQueries.QueryFindTenants(cmbTenantSurname.Value)
Me.subSearchTenants.Requery​End SubClick to expand...

Module:
Public Sub QueryFindTenants(sWhere)
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim sSQL As String
'Dim sWhere As String

Set db = CurrentDb
Set qdf = db.QueryDefs("qrySubSearchTenants")

'sWhere = Me.cmbTenantSurname.Value

sSQL = "SELECT tblTenants.TenantID, " & _
"tblTenants.PropertyID," & _
"tblTenants.TenantForename, " & _
"tblTenants.TenantSurname, " & _
"tblTenants.MobileNo, " & _
"tblTenants.HomeTelNo, " & _
"tblTenants.Email, " & _
"tblTenants.StartDate, " & _
"tblTenants.EndDate " & _
"FROM tblTenants " & _
"WHERE tblTenants.TenantSurname='" & sWhere & "' ;"​
qdf.SQL = sSQL
End Sub​Click to expand...



Just solved it, I changed the:
Me.subSearchTenants.RequeryClick to expand...

To:
Me.subSearchTenants.SourceObject = "Query.qrySubSearchTenants"Click to expand...
 

Read other 2 answers
RELEVANCY SCORE 66.8

Lets say I had in cell $A$1 the number "36", in row2 figures for monthly spend, in row 3 cumulative spend and in row 4 the month number(1,2,3,4..). What I want to do is substract the monthly spend from $A$1 months ago from my cululative figure in row 3 so I have a rolling cummulative hardware spend value of 36 months in this case. So in this case if I was to do it manually in cell AK3 I would have "=AJ3+AK2-A2" and drag it further right. So I add my monthly figure of spend each month to the cummulative figure in row three but also deduct the monthly spend from 3 years prior.

So in order to use the figure in $A$1 I could do something in row 3 like: starting from B3: "=if(B4>$A$1,A3+B2-[$A$1rows_further_back_thanB2],A3+B2). So when I get to month 37 in row 4 I deduct the figure in row 2 (in the formula in row 3 - the cummulative row) from 36 months prior. I hope this all makes sense. Can you help me please (see attached spreadsheet).
 

Read other answers
RELEVANCY SCORE 66.8

I need to provide seqential row numbers from 1 to N that relate to cells in another column when the cell value is greater than zero. For instance, if the column with data is column d and I have data greater than zero in cells d3, d4, d5, and d7 I'd like the row count in column c to read number 1 for c3, 2 for c4, 3 for c5, and 4 four c7. Cells c1 through c3 as well as c6 should remain blank. What formula do I use?
 

A:Solved: sequential row numbering based on value in another column/cell

Read other 7 answers
RELEVANCY SCORE 66

i have a spreadsheet that updates with data from a number of different sources. i need to filter only the relevant data that i need. i'm almost there but need to find out how to pull 2 more pieces.

in the attached spreadsheet the cells i need are in column R and T. I need to know the corresponding "name" and "size" of the data that is in column S.

unfortnuately the actual values don't show up in the spreadsheet b/c they are live data feeds but the formulas written up until now are still there.

in a nutshell what i'm doing is getting 5 price feeds and i need to filter out which is the highest updated within the last 2 minutes. i actually have the only the price figured out in column S, but i also now which name and how big the size is related to that price.

your help is very appreciated.

regards,
 

A:Solved: excel - data returned based on the value of a specific cell

Read other 10 answers
RELEVANCY SCORE 66

I have an excel sheet with two tables for customers to know which products the customer is using based on a Contract table.

The enclose sheet has two tables:
1. Customer
2. Contracts

All what I need is to fill the columns of "Product A", "Product B" and "Product C" of every customer looking at the table "Contracts" where the data is available based on the "Customer ID"

How can I do it?
 

A:Solved: MS Excel / Filling a cell with YES based on data from another sheet

I would just use a simple countifs formula to check if the customer ID and Product type match (ie the count of matches is greater than zero)
See attached.
 

Read other 3 answers
RELEVANCY SCORE 65.2

I need a code that will allow the workbook to be emailed when Column A is populated by certian numbers. The numbers in column A corespond to particular email addreses. This is the code I've been working but it isn't functional.

Sub Email_Out()
If Worksheets("Sheet1").Range("A5:A200") = "190030001" Then
ActiveWorkbook.SendMail Recipients:=("[email protected]")
ElseIf Worksheets("Sheet1").Range("A5:A200") = "190450025" Then
ActiveWorkbook.SendMail Recipients:=("[email protected]")
End If
End Sub

All help is greatly appreciated!
Mikey
 

A:Solved: VBA email excel workbook based on cell values using; If Then ElseIf Please he

Read other 16 answers
RELEVANCY SCORE 63.2

I have the following formula:

=IF(Estimate!D2=0,"",VLOOKUP(Estimate!D2,Pricing!$C$4:$G$200,2,FALSE))

Where is says "Estimate!D2" I would like the "2" in D2 to become a variable so it take its info from another cell. i.e. I want to enter a number in cell F20 , lets say 15. to replace the "2" in D2. So now my formula would get info from cell F20 and excel would see it as D15 now. I tried put it in brackets and quotes and I cant see to get it to work. can anyone help.

thanx
 

Read other answers
RELEVANCY SCORE 62

Hello,

I am trying to find a certain patter in a file which has the following format

key1 ; value1
key2 ; value2

and so on.

I intend to use the findstr command as follows

findstr /L key1 input_file

returns the following output

input_file:key1 ; value1

I need to be able to assign the value of "value1" into a variable "VAR1" that i can use in the script later. Any ideas as to how to do this. Or is there a better/easier way to get the same result.
As always your response/feedback is greatly appreciated. Thanks.

A:set variable based on output of seach string in batch

Quote:





Originally Posted by

returns the following output

[B


input_file:key1 ; value1[/B]

I need to be able to assign the value of "value1" into a variable "VAR1" that i can use in the script later. Any ideas as to how to do this. Or is there a better/easier way to get the same result.




One way to get to the value1 is as follows

for /F "tokens=2,3,* delims=;" %i IN ('findstr /L "key1" input_file') do @echo %i

this is a special format of the "for" command, which is splitting the output of the "findstr" command working on the *.ctr file name and returning only one of the tokens presented to the FOR command.

My next step is to be able to assign the result of the "for" command to a variable that I can use later in the script. Any ideas. Thanks.

Read other 3 answers
RELEVANCY SCORE 60.8

This post is from another board. I have seen this problem quite a while ago and resolved it (Excel 97), but can not remember what caused it.
As I "remember" the solution was relatively minor, but .....

Anyone have a clue?

Tx

"On my XP machine, for some reason recently if I open Excel 2002 and try to click anywhere on the spreadsheet, it will highlight a cell. Then I move the mouse and it keeps dragging an area to select. The problem is I click it another 10,000 times and it won't stop selecting cells. I have no clue why this mouse isn't working correctly. It works just fine everywhere else, but for some reason in Excel it suddenly found an error or something in the software and it seems to like it."
 

A:Excel - Cell Selection

Read other 7 answers
RELEVANCY SCORE 60.4

I recorded a macro and run the same code below which works.
So, I saved it as an xla add-in and ran the same code. It creates the pivot table
but I get the error below when it is about to fill the data for the row

Run-time Error '91':Object Variable or With block variable not set

This error on the procedure Sub Test() below occurs at line ActiveWorkbook.PivotTableWizard.
Any suggestion on how to fix this error?

Sub Test()

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"print_logs!R3C1:R6C40").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10

********* Error Message shows when executing line below

ActiveWorkbook.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Comment", _
ColumnFields:="Paper Size"
ActiveSheet.PivotTables("PivotTable1").PivotFields("Total Pages").Orientation _
= xlDataField
End Sub
 

A:Solved: Run-time Error '91':Object Variable or With block variable not set When Runin

change ActiveSheet to ActiveWorkbook.ActiveSheet
 

Read other 1 answers
RELEVANCY SCORE 60.4

Is there any known reason a user of workbook would get the error code 91, but someone else does not?
I cannot really debug the code since I am not getting the error and it's not practical for me to be at the users' machine, nor for me tell them which step to take to help me debug it.
 

A:Solved: Excel VBA error code 91: Object variable or with block variable not set

Read other 9 answers
RELEVANCY SCORE 60

is there a way to select many cells using the row and column number?
the range command permits to select knowing the letter and number of a cell, in 1 cell or many cells. range("a1:c5").select, or cells(5,12).select.

what I am trying to do is range(1,1:3,5).select. which I know it is wrong.

thanks
 

A:range vs cell selection in VB Excell

Is there any particular reason why you don't want to use the column letters in conjunction with the row number? Also, what is the purpose of selecting these multiple cells? Will you be copying the data to another worksheet? Please provide more details on what you are ultimately trying to accomplish.
We also need to know in your example above if you are only trying to select cells A1 and E3 or are if you trying to select the entire range of cells between A1 and E3 ?

Rollin
 

Read other 1 answers
RELEVANCY SCORE 60

Hi All,

Excel VBA
Error : Run Time Error 91 - Object variable or With block variable not set

I have a userform with some textboxes and Command Buttons. When I add
the record, I receive an error message. The Code I wrote on Command Button to save the data into worksheet is as follow:
Code:
Private Sub cmdAddScore_Click()

Dim LastRow As Object
Dim LastRow1 As Object
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim RESPONSE As Double

Set ws = Worksheets("Matches")
Set ws1 = Worksheets("Schedule")
Set LastRow = ws.Range("A65536").End(xlUp)
Set LastRow1 = ws1.Range("H65536").End(xlUp)

RESPONSE = MsgBox("Do you want to save this record?", vbYesNoCancel)

If RESPONSE = vbYes Then

LastRow.Offset(1, 0).Value = txtMatchNumber.Text 'Column A
LastRow.Offset(1, 1).Value = Format(txtMatchDate.Text, "dd.mmm.yy") 'Column B
LastRow.Offset(1, 2).Value = Format(txtMaxOvers.Value, "00.0") 'Column C

If optBattingFirst.Value = True And txtTeam1Name.Top = 150 Then
LastRow.Offset(1, 3).Value = "BF" 'Column D
ElseIf optBattingFirst.Value = False And txtTeam1Name.Top = 180 Then
LastRow.Offset(1, 9).Value = "BS" 'Column J
End If

If txtTeam1Runs.Top = 150 Then
LastRow.Offset(1, 4).Value = txtTeam1Name.Text 'Column E
LastRow.Offset(1, 5).Value = txtTeam1Runs.... Read more

A:Solved: Run Time Error 91 - Object variable or With block variable not set

Read other 13 answers
RELEVANCY SCORE 59.2

In cell j, I have formula =IF(SUMPRODUCT(ISNUMBER(SEARCH("VLXP",K2:AB2))+0)>=1,"Yes","No") that returns yes or no if VLXP is contained in any cell K2 through AB2 and it works correctly. What I would really like to do is then put into cell j the entire matching cell content or if not found return n/a. Is there a way to accomplish this maybe with VBA?
 

A:Solved: Excel if cell contains vlxp then put matching cell data in current cell

Read other 6 answers
RELEVANCY SCORE 58.8

Hi

I'm working in Access 2000. I created a database in which you select a course number and the course name automatically appears in the "course name" box. I created this database but cannot for the life of me figure out how I did that.

Now I need to do it again...I've looked at the properties of the fields in the form and everything, and cannot find where you point it to fill in one field based on a another.

Can anyone help me with this?

thanks
Bobbi
 

A:Access2000 -Automatically fill in fields based on a selection

Read other 6 answers
RELEVANCY SCORE 58.8

Hi,

I am new to access and am struggling on trying to delete a record. Basically, I want the user to select an option from a drop down list (Select_Project_Type_cbx) and then click on a 'Delete' button which will delete the record associated with the drop down option selected.

This is the code I have so far:

Private Sub Delete_Project_Type_btn_Click()
Dim sDeleteRecordSQL As String
sDeleteRecordSQL = "Delete * From Project_Type_tbl " & _
" Where Project_Type_Name.Name = " & Select_Project_Type_cbx.Value
Me.Select_Project_Type_cbx.RowSource = sDeleteRecordSQL
Me.Select_Project_Type_cbx.Requery

End Sub
When the user clicks on the delete button it just removes ALL options from the combo box. Nothing is deleted from the table "Project_Type_tbl".

If someone can point me in the right direction on where I am going wrong here it would be much appreciated!

Thanks,
Jack
 

A:Delete a record in Access based on a combo box selection

Read other 10 answers
RELEVANCY SCORE 58.8

REDIRECT SELECTION IN CELL TO PRINT JOB

In spreadsheet_1 I'm creating several cells which contain drop-down menus so the user can select values from other spreadsheets in the same workbook (Excel 2003).

My issue is:

I would like the user to, once all data is selected, be able to print automatically the spreadsheet_1 AND all the spreadsheets tagged in those cells with the drop-down menu when he selects print... Is it possible?

More info: the user will be choosing from a summary list that contains hyperlinks to the individual spreadsheets I want to actually be printed.

I'm new to programming and VBA, but I'm keen to learn!

Thanks in advance!

millawitch
 

Read other answers
RELEVANCY SCORE 58.8

Hi,

Have been fighting with a spreadsheet for a few days now, and after trying out various "solutions" found on the Web all to no avail I thought I would go the guys ( and girls) who know!

I have a spreadsheet ( attatched ) and the purpose of it is for me to be able to select reasons why hire vehciles do not pass a daily check. There are, sadly, sometimes occassions where there are more than one reason so I would like to be able to selct 1,2,3 or 4 reasons from my drop down box and have the results shown in the "same cell" seperated by a comma. I can then use that data in some charts on aanother sheet.

Does anybody know how I can do this.

I know I need some VBA code but when I have tried copying/pasting from examples on the web it doesnt work.

Please please please can someone stop me going mad!>!>?!?!

Thanks

Rob ( DJ_Jingles)
 

A:Excel multi selection list results into 1 cell

Read other 9 answers
RELEVANCY SCORE 58.8

REDIRECT SELECTION IN CELL TO PRINT JOB

In spreadsheet_1 I'm creating several cells which contain drop-down menus so the user can select values from other spreadsheets in the same workbook (Excel 2003).

My issue is:

I would like the user to, once all data is selected, be able to print automatically the spreadsheet_1 AND all the spreadsheets tagged in those cells with the drop-down menu when he selects print... Is it possible?

More info: the user will be choosing from a summary list that contains hyperlinks to the individual spreadsheets I want to actually be printed.

I'm new to programming and VBA, but I'm keen to learn!

Thanks in advance!

millawitch
 

A:Excel 2003: Redirect selection in cell to print job

Read other 16 answers
RELEVANCY SCORE 58.4

I have a spread sheet where I would like a message to pop up after I enter a value in cell G2 based on what the value of I2 is. like the following description:
In cell I2 I have "=(E2*F2*G2)/144" after I enter a value in cell G2 I want a message to come up if the value of I2/g2 is less than three and not equal to zero" Is this possible? If so could someone help me out with this?

thanx
 

A:validation based on another cell

This is the same as the challenge file but I added the code for this question
 

Read other 1 answers
RELEVANCY SCORE 58.4

I have a fairly simple task. From Row 17 to row 51 and Row 96 to 163 , I want to delete the row if cells P and Q are empty.

From row 57 to row 94 I want to delete the row if cells B, C, P, and Q are empty

I'm not sure what I'm doing wrong here.

Sub CleanUp()

Dim endrow As Long
Dim x As Integer

Set endrow = Sheets("Work Order").Range("A17").End(xlUp).Row

For x = endrow To 17 Step -1
If Sheets("Work Order").Range("P" & x) = "" And Sheets("Work Order").Range("Q" & x) = "" Then Sheets("Work Order").Rows(x).EntireRow.delete
End If
Next x

Rows("167:180").Select
Selection.delete shift:=x1Up
End Sub
Click to expand...

If you see something glaringly wrong or know a quicker way to do this, I would be very thankful.
 

A:VBA Deleting row based on cell value

Here's an example work sheet. If the code works right in the example, it would delete rows 4, 5, 9, and 10.

Code:
Dim endrow As Long
Dim x As Long

For x = endrow To 29 Step -1
If Sheets("Sheet1").Range("P" & x).Value = "" And Sheets("Sheet1").Range("Q" & x).Value = "" Then
Sheets("Sheet1").Rows(x).EntireRow.Delete
End If

Next x
Why won't it remove the rows?
 

Read other 2 answers
RELEVANCY SCORE 58.4

Hi, I am new to VBA so please excuse this question if it appears to be elementary. I am trying to write a code in VBA that allows me to operate scenarios in Scenario Manager, on an active worksheet from an inactive worksheet.

I have managed to write a code that runs the scenarios from a combo box actually on the active worksheet using 'Worksheet' 'Change':

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$O$16" Then
ActiveSheet.Scenarios(Target.Value).Show
End If

But am struggling to write a code that will allow me to operate the same scenarios from an alternative worksheet within the same work book. The ActiveWorksheet is called 'NGN Inputs' FYI and is "Sheet 14" and the Inactive worksheet (i.e. the one not containing the scenarios) is called 'Assumptions ("Sheet4")

Again this is probably fairly elementary but I am new to VBA and would appreciate any help.

Many thanks.
 

A:VBA - VBA to execute Scenarios Based On Combo Box Selection on an Inactive Worksheet

don't work with Activesheet, set up your procedure like this...
Code:
dim wb as workbook
dim WS1, WS2 as worksheet

Set WB = Thisworkbook

Set WS1 = WB.sheets("NGN Inputs")
Set WS2 = WB.sheets("Assumptions")
then you can refer to your cells like this..

WS1.cells(16,15)

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$O$16" Then
WS1.Scenarios(Target.Value).Show
End If

 

Read other 1 answers
RELEVANCY SCORE 58.4

Hey!

I am working on a school project in Access, but having some problems using VBA. I have an appointment and client table, with a store of addresses in the client table. there are address fields in the appointment table also, and i would like to be able to update these fields with the addresses from the client table based on a selection in a combo box on the appointment form.

I have experimented with some VBA code but have found nothing that works. I am not sure if the code is wrong or if I have it set to the wrong event on the combo box. I tried writing functions to fetch the correct field entry and place in the field on the form but it does not seem to have worked.

This is the code on my combo box (place)
Code:
Private Sub place_BeforeUpdate(Cancel As Integer)

If place.Value = 1 Then
meeting_building_name = GetBuilding
meeting_street_name = GetStreet
meeting_town_city = GetTown
meeting_county = GetCounty
End If

End Sub

This is one of the functions I use (they are all very similar)
Code:
Function GetBuilding() As String

Dim db As Database
Dim Lrs As DAO.Recordset
Dim LSQL As String
Dim LBuilding As String

'Open current database connection
Set db = CurrentDb()

'SQL statement to retrieve building value from table
LSQL = "SELECT clients.building_number FROM clients"

Set Lrs = db.OpenRecordset(LSQL)

'Retrieve value if data is found
If Lrs.EOF = False Then
LBuilding = Lrs("building_number")... Read more

A:Access - Update text boxes based on combo selection?

Read other 6 answers
RELEVANCY SCORE 58.4

Hi there guys

Looking for help in excel 2007 for the macro part.

Basically in sheet1(BOM), we have configurations which our customer order to us (in the form of CP1,CP2..CPn). In Sheet2 (CP) details about CP are mentioned, basically each CP has list of items under it. Typical order from customer includes combination i.e. CP1&CP2 or could be CP2&CP3. What I want to do is based on the order in BOM sheet, need to search the details of CP's in sheet2 (CP) & copy the rows belong to that particular CP in the BOM sheet. So that I can make the BOM list for that particular order. Each CP has variable number of rows. Appreciate your help in making macro for this.

Rgds, MintC
 

A:Excel Macro to copy multiple rows based on selection

Read other 11 answers
RELEVANCY SCORE 58.4

Hi all,
I'm trying to create a form in Word that displays a table with fillable questions based on the results of a dropdown selection. So, for example, if I were asking how you get to work every day, the choices might be walk, bicycle, or drive.

If you choose walk, I have the following questions:
Distance: [text box]
Time required: [text box]

If you choose bike, I have the following questions:
Distance: [text box]
Time required: [text box]
Storage location: [text box]

If you choose drive, I have the following questions:
Distance: [text box]
Time required: [text box]
Parking location: [text box]
Monthly cost of parking: [text box]

I only want the relevant questions to show up for the option you select. I've tried this a couple of different ways, but I'm stuck. I can use a nested IF statement in the left column of the table and then use a text form field in the right column. If I do that, then the excess rows for the walk and bike options show a blank left column and a fillable form field in the right column, which is confusing.

I've tried using a nested IF statement for the whole table, but Word doesn't allow form fields within the IF statement (*shakes first at Word*).

I feel like my best option is to create a table for each option, and then show or hide the appropriate table based on the option selected using a macro. The problem is that I have no idea how to do that. I know that the preferred answer is not to do this in Word, however I'v... Read more

A:Show fillable table based on dropdown selection in MS Word

Read other 12 answers
RELEVANCY SCORE 58.4

Hello.
I am writing a database to monitor the fitting of kits to vehicles. What I want it to do is as follows:

1) I will have one combo box with vehicle Models in it (approx 30), another with Derivatives in it (approx 15 per Model), and another with Kit Names in it (approx 3 per Derivative). By selecting a model, I want the list of Derivatives to be limited to show only the appropriate Derivatives. Based on the Derivative selected, I want the list of Kits to be limited to only show the appropriate ones.
2) When I select the kit, I want to return a list of the Kit's contents. A Kit consists of between 1 and 6 parts. Each part has several fields - Part Name, Part Code, Part Price, Fitment time (which will be multplied by an hourly labour rate in queries) and some parts have an additional Materials cost.
Number 1) above, I think I can do based on info I found on the net.
Number 2) above, I'm less sure of - how do you return a list of items that varies in size depending on your selection? Fundamentally, I need to work out how to set up the tables to store this data. My initial thoughts were I'd have a seperate field for each Part Number, but now I find there are up to 6 parts, it would mean selecting the Kit would return 24 fields of information (6 Part Name fields, 6 Part Code fields, 6 Part Price fields, 6 Fit Time fields and 6 Materials fields), which seems totally impractical.

Other things to note:

- The part details such as Fitment Time and Parts Price... Read more

A:Access 2002 - return a list based on combo box selection

Read other 7 answers
RELEVANCY SCORE 57.6

Hello everyone.

I am trying to tweak a macro that was posted on this forum some time back, but I'm a bit of a N00B. The macro is from this thread that is now locked: https://forums.techguy.org/threads/...ue-date-reminder-based-on-excel-file.1129238/. What I am looking to do is generate two different emails whenever the value of a cell changes. All the data that needs to be part of the email is included in various cells. I've included sample data. If possible, could you provide code for generating the email and another for sending the email without opening Outlook (i.e., as soon as the value changes)? I've included sample data for your consideration. Thanks in advance for any help you can provide.

When Cell Value = In Progress
The email I'm hoping to generate (I've mapped it with the cell contents I'm hoping to pull) will be as follows when Column F changes to In Progress. The email addresses are in Column G.

Subject Line: "Web Request(s) Now In Progress"

Dear D2,

Your C2 B2 request has been received and is now in progress. Thank you!

My Signature

When Cell Value = Completed
The email I'm hoping to generate (I've mapped it with the cell contents I'm hoping to pull) will be as follows when Column F changes to Completed. The email addresses are in Column G.

Subject Line: "Web Request(s) Completed"

Dear D2,

Your C2 B2 request is complete and the changes are now live. Thank you!

My Signature
 

Read other answers
RELEVANCY SCORE 57.6

Hello,

I'm trying to enter a formula that returns a value when a loan balance is paid off. I've attached a spreadsheet that shows the costs and revenues of a project and the corresponding loan balance. It's very basic, but I'm trying to see if there is any formula that will return the Month that corresponds to the loan being paid off. I've been trying to use an "if" formula and when the loan balance is <0, but i don't know how to do it with just one cell. Let me know if anyone has ever done anything like this.

Thanks,
Chet
 

Read other answers
RELEVANCY SCORE 57.6

Hello everyone,

I'm trying to create a macro that will run one set of commands if someone changes a drop down menu from YES to NO, and run a different set of commands if the drop down menu is changed back from NO to YES. I've found macros that will run when a cell is changed, but I'm not sure how to write an if statement on the macro, that will run it one way or the other, based on the cells value.

The option defaults to YES. If they change it to NO, I want it to unhide the rows and enable overwriting of the unhidden cells. If they change it back to YES, I want it to automatically hide the rows and put the formulas back in. Here is the file.

Any help is greatly appreciated!
 

Read other answers
RELEVANCY SCORE 57.6

Hi,

I am a total excel amatuer and would really appreciate some help.

Im working on a basic doc and want to keep away from VB and macros. All I want is a chosen cell to auto populate with data depending on what is chosen from a drop list. I have looked at dependent drop lists but even if the dependent list has only one choice you still need to click on the second list to see that one option.

Say the drop down list has 3 choices (walk, drive and bus).

So if for instance you chose walk from the drop down list, another cell would automaticaly populate with "leave the house and start walking". Likewise, selecting drive gives "leave the house and start driving"

There must be an easy way to do this, no?
 

A:auto populate a cell with data when a selection is made from drop down list

Read other 8 answers