Free Microsoft Excel 2013 Quick Reference

VBA code to search for data from a sheet and output in a listbox

hi,
i have prepared a form which searches for data items in a sheet and displays the results in a listbox, have tried much myself but whatever i've done isn't working for me, so any help will be greatly appreciated. also went through previous posts but solutions not wrking for me...


Related Results

  1. VBA code to search for and delete Form Controls on an Excel 2007 spreadsheet
  2. VBA Code to Search/Edit Data Populated by Userform
  3. VBA code to search for a word and return the values of numerous corresponding cells.
  4. Select only unique fields from a range and sum quantities against them
  5. VBA code to import specific data from one excel workbook to another excel workbook
  6. Extract data from one sheet and insert in other after matching the names
  7. VBA Coding for extracting data from a Pivot Table
  8. VBA code to search files based on last modified date
  9. Code to pulling specific data from a sheet
  10. VBA Code to delete rows which contain a certain text
  11. I need to search for then extract a specific portion of cell data...
  12. Code to Query SQL Server with a parameter value (entered into an Input Box), and have that value also display in a selected cell on a worksheet
  13. Search & copy data from any of 100 columns in closed WorkBook without opening it
  14. Using VBA code to extract web data
  15. Code to search for the next cell with zero
  16. Getting Excel whilst using the sumif formula to search for part of a word
  17. How to write macro for copy data from multiple sheet to a single sheet.
  18. Selectively transferring data from a text file
  19. Append to Excel 'Template' data from other Excel workshee
  20. Auto copy specific data from one sheet to other
  21. VBA code to create new workbook and e-mail once a week
  22. URL in cell used in VBA code to import web data ...
  23. Vlookup to search for data across multiple sheets
  24. VBA Code to duplicate (create copy of a sheet) sheets?
VBA code to search for and delete Form Controls on an Excel 2007 spreadsheet

Greetings,

I have created a simple userform to add data to an Excel 2007 worksheet; the form contains two buttons, one to add new rows of data and the other to close the form. The worksheet has been populated with data by users using the userform and now I'd like to add functionality to allow the users to search for data, and to edit data that was found during their search, in addition to adding new rows. My code appears below - I've removed many of the fields that show up on the userform to simplify the code (there are actually 74 columns). Thanks in advance for any assistance.

Best Regards,

Paul
*********************

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("ContractData")

'find first empty row in database

iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'ensure user has entered a Requested Document Type

If Trim(Me.cboDocTypeReq.Value) = "" Then
Me.cboDocTypeReq.SetFocus
MsgBox "Please enter a Requested Document Type"
Exit Sub
End If

'copy the data to the database

ws.Cells(iRow, 1).Value = Me.cboDocTypeReq.Value
ws.Cells(iRow, 2).Value = Me.txtFinDocNum.Value
ws.Cells(iRow, 3).Value = Me.cboInstitution.Value
ws.Cells(iRow, 4).Value = Me.txtRefDocNum.Value
ws.Cells(iRow, 5).Value = Me.txtProjStDate.Value
ws.Cells(iRow, 6).Value = Me.txtProjDur.Value
ws.Cells(iRow, 7).Value = Me.txtPRNum.Value
ws.Cells(iRow, 8).Value = Me.cboRequester.Value

'clears the data from the form

Me.cboDocTypeReq.Value = ""
Me.txtFinDocNum.Value = ""
Me.cboInstitution.Value = ""
Me.txtRefDocNum.Value = ""
Me.txtProjStDate.Value = ""
Me.txtProjDur.Value = ""
Me.txtPRNum.Value = ""
Me.cboRequester.Value = ""
Me.cboDocTypeReq.SetFocus
End Sub

'close form

Private Sub cmdClose_Click()
Unload Me
End Sub

'force use of button instead of using "X" to close form

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the button!"
End If
End Sub

Hello, I am new to this forum, and am just starting to get involved heavily in Excel (business functions).
I am looking for VBA code to search a worksheet for a specific word, and then return corresponding values next to that text. I have attached a sample. I am new to VBA, and tried to google/search the coding, but to no avail. Please see the attached spreadsheet, I have added notes in it.
Thank you in advance.

Afternoon all,

Looking for some help...

I have a range of stocks with quantities. Stock items have differing weights, lengths and depths merged into one field (i.e. "100.50.10" for 100cmx50cmx10cm). and each stock item has a value attached (£50, £100, etc).

Looking for some basic VBA code I can follow to select this data from a range and post in a new sheet summaries for unique stock items. For example, we may have 3 lots of 100.50.10 on stock in different locations with values of £10, £20 and £70.

I'd want the results to summarise that for 100.50.10 we have £100 worth of stock.

All help appreciated!!

Thanks,

Leon.

What I want to do is create a bottom in excel 2007 workbook (FileX.xlsm) that when pressed will open a window that will allow me to chose an excel workbook (FileY.xlsx) to import the data from, and input it into "FileX.xlsm". What I would like to do take data from "FileY.xlsm" that is in say “A1 to AN1” and import it into “A1 to A40” in "FileX.xlsm". If anyone has done something similar to this I know enough to piece it together with a little bit of help.

Thank you in advance

Hi Friends,

I am looking for a formula which can extract date from one sheet and insert in a particular column in other sheet after matching the criteria.

Like Sheet 1 has
Number Name
1 Ram
2 Sam
3 Pam

Sheet 2 has
Name number
Ram ? i want a formula to search for the same name in sheet 1 and reflect the number.

Attached sample sheet

Please suggest.

Thanks for all the help in advance.
Vijay

Hello All
Desperately need your help!!

I am trying to get a VBA code which will take data from a pivot table and put this into a new table.

The problem is the cell each week changes due to the data inputted into the Pivot Table. How can I create a VBA code that will pick up the desired cell each time even if this is in a different place.

Is there a way of coding this?

Thanks for your help in advance.

Regards

Hi all,

Another query regarding searching of files. This topic in VBA is not easy, at least to my standards.

This time, I want to search a folder which contains hundreds of data files. I want to search for those files based on the Last modified date/time. I want to search those files and copy them to a different destination and open them to copy a particular range of cells to another new work sheet for processing.

For example: I want to search for those files that were last saved in a particular month/week.

The code i need is to search and copy files at destination folder based on date range that I specify and open them. I hope that I can manage with the rest of the code.
I would like to input the date range at a particular cell in my worksheet which the code can access to perform the search.

Thanks in advance.

Cheers
-Yogesh

I have one sheet which is comprised of data that was imported from a web page. In a second sheet I have a cell asking for min price and max price which comes from the imported data. The min price is the 1st number and the max price is the second number. The issue is that the data can move to different cells when it's refreshed so my code needs to look for some specific wording to find the prices that I need rather than just directing it to the same cell each time.

The prices will always be in this string found on the cattle summary sheet, the prices are the numbers after lbs:

SLAUGHTER BULLS: Yield grade 1-2 1000-1500 lbs 0.00-0.00;
1500-2000 lbs 71.00-75.00.

Hello

I have referred to a previous discussion called "VBA Code to delete rows which contain a certain character" but whilst this has helped, I could do with a bit more guidance if possible.

A sample of the data I'm working with looks like this (in column C):

Microsoft Office XP Standard
Security Update for Windows XP (KB927802)
Windows XP Hotfix - KB918439
Windows Installer 3.1 (KB893803)
Shockwave
Squirrel SQL v2.1
Update for Windows XP (KB911280)
Screensaver
etc...

I would like to find out the best way of deleting all the rows that contain the following text:
"hotfix"
"security update"
"windows installer"
"update for windows XP"

I was thinking along the lines of the suggestion offered in the "VBA Code to delete rows which contain a certain character" - to use autofilter but even using macros, I am struggling to turn this into code.

Any help would be much appreciated.
Thanks
Grace

I need to search for then extract a specific portion of cell data. Below, I
have provided an example of what I'm trying to do.

Column "A" (raw data) Column "B" (end result)
700001103 (x4) 4
8675-US (59) 59
8675-EU x 5 5
6330-02(18) 18
6330-02CE (x10 ) 10
6305-02 (1) 1
58516 (x117) 117

I hope someone can help me.

Thanks.
//Ken

I'm wondering how BEST to accomplish this.

I have a EXCEL workbook which populates data to, and retrieves data from SQL
Server.

I currently have VBA code which effectively sends the data from the EXCEL
worksheet to SQL Server tables -- no problem with this.

I currently have VBA code which effectively retrieves data from SQL Server
and displays that data back into the worksheet -- no problem with this
either.

I'm currently using a Command Button (on-click event) and the code below to
query SQL Server to determine the Max Version Number and add 1 to that
value. This value is then passed to a worksheet cell, and rolled up to SQL
Server along with the rest of the data to be submitted -- no problem with
this either.

Here's my "GenerateVersionNumber" code ...
===============================
Public Sub cmdGenerateVersionNumber_Click()
' Create a connection object.

Dim cnExcel As ADODB.Connection

Set cnExcel = New ADODB.Connection

' Provide the connection string.

Dim strConn As String

'Make Version the active sheet & Clear Data

'ThisWorkbook.Worksheets("Version").Range("A2:A150 ").Clear

'Use the SQL Server OLE DB Provider.

strConn = "PROVIDER=SQLOLEDB;"

'Connect to the XXXX database on the XXXX Server.

strConn = strConn & "DATA SOURCE=xx.x.xx.xx;INITIAL CATALOG=XXXX;" & _

"User Id=xxxx;" & _

"Password=xxxx"

'Now open the connection.

cnExcel.Open strConn

On Error Resume Next

' Create a recordset object.

Dim ProdID As String

Dim sqlCommand As String

Dim rsExcel As ADODB.Recordset

Set rsExcel = New ADODB.Recordset

ProdID = InputBox("Enter Product ID.")

sqlCommand = "SELECT IsNull (max(Version), 0) + 1 FROM Products WHERE
[ProductID] = '" + ProdID + "'"

With rsExcel

' Assign the Connection object.

.ActiveConnection = cnExcel

' Extract the required records.

.Open sqlCommand

' Copy the records into cell B2 on Sheet15 (Product Tracking Overall)

Sheet15.Range("D2").CopyFromRecordset rsExcel

' Tidy up

.Close

End With

cnExcel.Close

Set rsExcel = Nothing

Set cnExcel = Nothing

End Sub

==============================

Here's what I'm tring to do ....

I don't want the Sale Team to have to enter ProductID more than once.
ProductID is a field that needs to be entered on a worksheet, and it's also
a Query Parameter which I send to SQL Server to generate my One-Up Version
Number. How can I modify my "GenerateVersionNumber" sub (shown above) to
not only query SQL Server with the ProductID parameter entered in the Input
Box, but to also send the value entered in the Input Box to a particular
cell on a selected worksheet?

Thanks in advance for any assistance/advice offered.

Hi everyone,

I urgently need a kickstart with VBA code.

With value from a cell in Active Sheet, Search & Copy data from any of 100 columns in closed WorkBook without opening it, and then paste it to nth row of Column x in Active Sheet.

Any help will be greatly appreciated!!

Hello everyone. On a monthly basis I have a procedure to extract data from a website and place that data into an excel worksheet. it does not take long to do, as there is a "button" on the website that you can press to "dump-to-excel". However I would like to use VBA code to inlcude this procedure into some macros I have already developed. The only "variable" in the procudeure involves the user inputing dates in the following format: dd-three letter month abreviation-YYYY. The web data is contained in a nice table in the center of the site. The number or rows varies, but the number of columns is fixed at 5. The intranet site uses IE. The following code sort of works, but as you can tell it does not prompt the user to enter a date. The code below is a good start, I think.

Sub WB()

URL = "http://cds.worldbank.org/Pages/CurrMain.aspx?rate_date=31-mar-2010"

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True


'get web page
IE.navigate2 URL & Ticker
Do While IE.readystate <> 4 Or _
IE.busy = True

DoEvents
Loop

Do While IE.document Is Nothing
DoEvents
Loop


End Sub
I would like to save the table into a worksheet called CDS_Raw.xlsx. Any suggestions would be greatly appreciated. Note that the code above does bring the website open.

I am assuing I should declare a variable say fdate

and use that somewhere in the URL?

I use the following code to search for the next cell with a zero. But I found it stops if the value has zero contained such as 8.04, and I would like it to stop only when the value is "0".

The cell formula is an indirect reference such as =INDIRECT($A192&J$187) in which there is no data yet, so the cell shows 0

Cells.Find(What:="0", after:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
Is there a way to have it pass over 8.04 and stop at the cell that has only a zero contained?

I am using a Sumif formula on a summary page that summarises from a list in
another sheet. This is case and word sensitive, is there any way to get excel
to search for part of a word e.g instead of searching for Cabin Installers
Ltd it could find Cabin?

I am just starting to learn VBA is this something VBA could easily do?

This same workbook i basically a monthly cost spread sheet that comes in and
i have to analysis to show the movement in the month. Is there anyway excel
could auto summarise the sheet e.g For every individual name/company on the
sheet it could find the sum of their costs?

Any help would be most appreciated

Kind Regards

Grant

Hi,
I want to copy data from multiple sheet and paste in a summary sheet. Every days the number of increases, from where i need to copy the data. Can we write a macro such that it automatically copy the data from all the sheet for the specified field and past in summary sheet.

In my workbook there are suppose 20 sheet and i want to copy the data only from 15 sheet. I have created two sheet blank to make boundary to identify the sheets need to be copied. Header is A2 to A15 and data is in B2 to B15 of each sheet. In the final sheet where i want to copy the data i have already added header as A2 to A15, now i want to just copy the data from cell B2 to B15 of all the sheet.
Please help me on this.

I'm looking for some Excel VBA code which takes price data from
a CSV text file like the one below and looks for the same initial
three-letter code in the spreadsheet, then inserts the price (from
the sixth column into the price column on the spreadsheet.
It needs to loop through the whole text file of course.
Anyone have some code which might do this or something
similar please?

AAC,01,134,134,130,130.5,5094
AAF,01,0.4,0.5,0.4,0.5,15000
AAM,01,15.5,15.5,14.5,15,3170
AAR,01,2.6,2.6,2.6,2.6,1000
AAX,01,377,384,375,377,5212
AAZPB,21,5800,5860,5800,5860,11
ABB,01,895,901,889,891,6367
ABC,01,226,229,224,228,49135
ABP,06,36.5,37,36,36.5,27157
ABQ,01,16,16.5,15.5,15.5,2090
ABU,01,2.4,2.6,2.3,2.5,11386
ABY,01,71.5,71.5,66,66,15150

Sample spreadsheet:

ABC 15/07/2009 122 $16.41 $16.94 3.23%
BHP 15/07/2009 60 $34.14 $37.50 9.84%
BXB 15/07/2009 348 $5.75 $6.00 4.35%
CBA 15/07/2009 52 $38.54 $39.76 3.17%
FGL 15/07/2009 376 $5.32 $5.51 3.57%
NAB 15/07/2009 86 $23.24 $22.26 -4.22%
RIO 15/07/2009 40 $49.90 $57.55 15.33%
SUN 15/07/2009 302 $6.62 $6.85 3.47%

I wonder is someone can help me to achieve what I thought was a fairly simple task. I have an Excel worksheet (my 'Template' worksheet) with just header (column) names, to which I need to append the data from a similar worksheet, but in this 'Data' worksheet to be imported, although the column header names are the same, they are not necessarily in the same order as the 'Template' worksheet. For example, in the 'Template' that I need populated, the first 3 columns may be named 'Code', 'Name' & Area, and whilst these same columns will always exist in the 'Data' worksheet, they may be in column position 7, 12 or 18. There are a total of 40 or so columns that need to be populated in this way and the formatting of the matching columns will already have identical formatting.
Thank you for your help
Robin

Hi

I just want to check that if it is possible to copy specific cells from one row to another sheet by using macro or any other script.

Attached is a sheet which explains what exectly I want

The sheet one is having Source data
Sheet three is the result sheet

If I put some code numbers (in number form) it should search the data from source sheet and update the same in result sheet in different rows

Please help

I need VBA code to select particular ranges from multiple worksheets and create a new workbook which is then e-mailed once a week. Any suggestions? I am new to Visual Basic and can figure things out if I have a starting point. I cannot write from scratch.

Thanks,

SSoutherland

Hi Everyone,

Within VBA code to import web data, instead of spelling out a URL as source is it possible to specify a cell containing the URL such as B2 of Sheet1 ...

Thanks,

Have a great day!!

i have a report that basically goes like this:

for the raw data, column Z is used to indicate the date (week ending) when an agent is cleared of a certain stage. a sample formula goes like this:

=IF(O3106="Stage 1",IF(OR(O3106="Stage 2",O3106="Stage 4"),"Not Yet",IF(VLOOKUP(G3106,'file location[scores db.xls]WE 4.04 - 4.25'!$A$1368:$Z$2500,26,FALSE)="Yes",VLOOKUP(G3106,'file location[scores db.xls]WE 4.04 - 4.25'!$A$1368:$Z$2500,25,FALSE),"Not Yet")),"Not Yet")

before we continue, some info as well on the other columns:

-column G contains the agent's name
-column O is used to indicate the agent's current stage

here's also some info on the scores db.xls file:

-the scores db file contains uploaded data from all worksheets and i've divided them per month, so the WE 4.04 - 4.25 sheet refers to worksheet data uploaded from week ending april 4 up to april 25
-column Y contains the week ending for that specific worksheet uploaded (example, if it was uploaded last april 16, it will be bucketed under week ending april 18, and if it was uploaded april 19, it will already fall under week ending april 25)
-column Z contains a formula that will return "yes" if certain worksheet questions did not have any markdowns at all, and will give "not yet" if there is at least one of the selected worksheet questions that had a markdown

basically the formula states that if the agent is in Stage 2 or Stage 4 as indicated in column O, it will leave the agent's status alone since they can't be cleared in stages 2 and 4. otherwise, it will look for that agent's name from the range specified.

i know i could have just used one big sheet instead of using multiple sheets but with the amount of data we accumulate every week, it won't be long before it gets unwieldy (there were some instances in the past which made me split them roughly into per month where an agent won't appear when autofilter is used unless they are filtered first by their team lead, or even by their team lead's manager, and so on). anyway, the question now is, taking the sample formula above, would it be able to make it continue to look for more data in another sheet once it hits the end of the data for sheet "WE 4.04 - 4.25", let's say sheet "WE 5.02 - 5.30"?

Thanks!

Dear friends,

I am a teacher and tryng to create a solution to mintor progress of my students whil they are working on the projects. The project monitorng/marking is based on different strands.

I entered all the strands on sheet (Data) the way I want them to be.

On second sheet (Students) I entered student names.

Now I want to put code to generate one copy of the sheet containg marking/monitoring table (data) for each student.

Each copy must be named after the student,,,,,

I will be obligd if you please help me.

Regards