Over 1 million tech questions and answers.

excel vba programming code

Q: excel vba programming code

when i try to make an vba code in excel vba extension at the moment i added
this line to the code of a button:

Range("A1").End(xlDown).Offset(1, 0).Select
it says there is an error but i did exactly what they said on this video
https://www.youtube.com/watch?v=CzP8nO9UVvY
its explained just after 25:12 minutes

RELEVANCY SCORE 200
Preferred Solution: excel vba programming code

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: excel vba programming code

I tried the code and it works without errors. Are you sure it's that line it complains about?

This shorter one works to: Range("A1").End(xlDown).Select

Read other 9 answers
RELEVANCY SCORE 72.4

I am writing a food costing program using excel. The program is designed to pull prices from various tabs(different suppliers) to a main Supplier Costing Page containing all the information on products for all suppliers. The code allows the user to enter in the name of the product, then uses the LOOKUP function to search for the product and returns the value in that row. The code I used to pull the information from the different suppliers works properly:

A19 value Steak Strip Loin C/C St. Silver FRSH

=LOOKUP(A19,Centennial!$A$6:Centennial!$A$100,Centennial!$R$6:Centennial!$R$100)

Centennial is the tab for that supplier. It pulls the correct value from the page 0.74, to cell R19
The problem I am having is that when I try to use this format of code on another tab it does not return the correct value:

=LOOKUP(A33,'Supplier Costing'!$A$6:'Supplier Costing'!$A$100,'Supplier Costing'!$R$6:'Supplier Costing'!$R$100)

The code should return the same value of the previous line of code as it is the same product:0.74 to cell R33 this time, however it returns the value of another product on the Supplier Costing tab. Is the LOOKUP function not searching the tab for the whole string? Any help or suggestions would be extremely helpfull. Thanks
 

A:Solved: Excel Programming Code Produces Wrong Value

Read other 12 answers
RELEVANCY SCORE 54

I am trying to change the D53 cell reference in this statement to a variable defined as integer, without success. I would be willing to change the whole range definition to a variable if necessary. Any suggestions would be appreciated!!

ActiveChart.SetSourceData Source:=Sheets("WorkSheet").Range("A29:d53"), _
PlotBy:=xlColumns
 

A:Excel VB Programming

Welcome to the board, Bob W.

Where will the values come from?

x = Application.InputBox("How many rows?", "Size chart range")
y = Application.InputBox("How many columns?", "Size chart range", 4)
ActiveChart.SetSourceData Source:=Sheets("WorkSheet").Range("A29").Resize(x, y), _
PlotBy:=xlColumns

, perhaps.

HTH,
bomb
 

Read other 1 answers
RELEVANCY SCORE 54

I was asked to make a program using excel for the main purpose of managing shares, stocks, investments and others. That's why I am looking for good tutorials for excel programming, for this will be my first time doing something of the sort.
If any1 could give good recomendations and/or tips, it would be fantastic.
Thanks in advance guys. As usual, life savers ;)

Just noticed that Excel Programming is VB. So also comments on this area would be helpful.

A:Excel Programming

Are you asking how to set up a spreadsheet to "manage" investments, or perhaps about Macros to automate the process? I think it would help us if you could clarify what you expect the spreadsheet to do.
Thanks,
John

Read other 5 answers
RELEVANCY SCORE 54

Say, for example, I have a list of interest rates and the more people I sign up to my program, the higher my interest rate is. The time money would be invest for, is on the left-hand side, and the amount of people signed up is on the top.The items listed down on the left-hand side are as follows:~ Weekly~ Fortnightly~ Monthly~ 2 Months~ 3 MonthsThe top of the cells are from 1 - 10.The interest increase both as they go down, and across to the right.My question about this, is if I were to have a different table, and in that table I have people who sign up people, and I have (I have cell referenced each part)A1 - NameA2 - People signed upA3 - Interest rateB1 - JohnB2 - 3B3 - (the interest rate)C1 - AimeeC2 - 7C3 - (the interest rate)I was to know that from the first table set up, how can I get Column 3 to represent the cell from the amount of people that have signed up. So, that in Column 3, the interest rate that is shown is from the first table, and gets the amount of interest from the people signed up.No, it's not just simple "cell referencing" because I want the interest rate to be based on both the, time period, and how many people signed up, and I want it to be automatic instead of me doing it manually.Is it possible?NOTE: Here is a perfect example of what I want to achieve, just some things change.

A:Excel Programming?

I'm not an Excel guru but I'm sure that you could make something work with either a combination of VLookup and HLookup or just Hlookup and an if statement.Here's a pretty good link on embedded vlookups. hlookups work the same way except it searches by the row rather than a column.http://pubs.logicalexpressions.com/Pub0009...icle.asp?ID=446

Read other 2 answers
RELEVANCY SCORE 54

Hi

Id like to set Toggle Button 1:

When Value to show Cell A1
Else, cell 1 to be hidden.

Advice please!

Thanks!

M
 

Read other answers
RELEVANCY SCORE 54

I was just wondering if anyone could help me with writing a macro in excel. I am fairly new to VBA. I am trying to set up a Macro that will chart data and display values above a target line green and values below the target line red. If anyone can help me with this that would be great!

Thanks,
Matt
 

A:VBA programming on excel

Hi cub18,

Welcome to the forums.

Plenty of people will help you on here, but you need to give a bit more information, and an attached spreadsheet is always a great benefit when attempting to solve issues like this.

It sounds like you just need to use conditional formatting, but again it depends on the details.
 

Read other 3 answers
RELEVANCY SCORE 54

I am developing a system using java and am in need of a java code that allows a person to enter a password only three times. please help.
 

Read other answers
RELEVANCY SCORE 53.2

help. i've installed qb 2009 on my laptop. i already have the validation issued by the company. where can i input the code AFTER installation?
thanks. lee

A:quickbooks pro 2009 validation code [from programming]

Hi Lee,

Not sure if 'QB 2009' is 'QuickBooks 2009' but I'd contact the Software Manufacturer for technical assistance as this is the Windows XP forum, for Operating System issues.

http://www.quickbooksheaven.com/contactdetails.htm

Cheers.

Read other 1 answers
RELEVANCY SCORE 53.2

Hiya

This is the sample code for Applied XML Programming for Microsoft® .NET, 0-7356-1801-1.

System Requirements

To run the sample applications, you'll need the Microsoft .NET Framework and Microsoft Visual Studio .NET. Most of the samples also require Microsoft SQL Server 2000. A few samples make use of Microsoft Access 2000 databases, and some require SOAP Toolkit 2.0 and SQLXML 3.0.

For more detailed information about the System Requirements, refer to the book's Introduction.

Operating System - Windows 98, NT4 & 2000, Windows Me, Win XP

http://www.microsoft.com/downloads/release.asp?ReleaseID=42809&area=search&ordinal=5

Regards

eddie
 

Read other answers
RELEVANCY SCORE 53.2

I have two spreadsheets; the first has two columns, UPC and ITEM; the second has many columns of which UPC is one. I need to create a lookup function or something that will insert the value of ITEM from the first spreadshet into a new column I will create in the second spreadsheet based on the UPC matching. I prefer to use Access for these situations but I don't want to do that with this data. Is this easy to do?
 

A:Excel LOOKUP Programming

Yes. Give me 5 minutes & I'll post a formula. Or does it have to be "programming"?
 

Read other 3 answers
RELEVANCY SCORE 53.2

I want to use MS Excel for keep records of a warehouse. I have created an invoice with necessary formulas and it automatically calculates everything, writes the final total in words. Now I want to connect this invoice with a warehouse record to automatically keep records of invoices issued and to automatically reduce the quantity of goods from the warehouse. This should help me to find the availability of goods at the warehouse at any given time and also to see the sales progress.
The problem is I don't know the formulas to be used for this type of programming.
Please help me with some idea about the formulas to be used.
Thank you.
 

A:Microsoft Excel programming

Read other 11 answers
RELEVANCY SCORE 53.2

Anybody know how to format a cell like this?

I would like my cell to total up a bunch of items, much like the Epsilon (at sum), but then I want the display font to be blue when it is positive, and then red when it is negative.

Thank you.

------------------
"There are no stupid questions, just stupid people." -- Mr. Garrison on South Park
 

A:Excel Programming... or formatting

Highlight the cell, rows or columns you want formatted. On the Menu-Format>Conditional Formatting. Using the
Cell is: Greater than: 0 and
Cell is: Less than: 0
and specifing the patterns or font you want for each particular scenario should get you what you want.

The above uses two Conditional Formats you may be able to get your conditions set up using only one box. It also leaves 0 without a format so you may want it to be inclusive on the negative or positive result.

 

Read other 3 answers
RELEVANCY SCORE 53.2

Hi Guys,

Let me start off by stating that I'm still trying to learn all the juicy functions you can do with Excel, and I've just gotten myself a little project where I think I can learn but I need some kind of help from you guys.

Basically what I'm trying to do is somehow automate a creation of a report via excel, first method I thought of was to use macros, but since the data that'll be included in the report will be on a different cell each time, macro might not work as I imagined it to be, then I thought of the IF function, I have not yet tried doing that, but would that work? Can you please share your thoughts? Ask me any additional info and i'll try to explain it as simple as I can.

Cheers.

A:[HELP] Programming in Excel 2013

Your description is entirely too vague in order for anyone to offer any kind of help. Can you be more specific as to what kind of a report you want to generate and what data you will be using?

Perhaps you could post a sample of the worksheet with the data and a sample of what you want the report to look like.

Read other 2 answers
RELEVANCY SCORE 53.2

I am writing a food costing program in excel to calculate a number of costs, conversions etc. in Microsoft Excel. I have my code working to what I would like it to do but I have hit one snag. I want to be able to copy and paste the code throughout the spreadsheet and I need certain values to remain the same and some to change accordingly.=LOOKUP(A13,Centennial!A6:Centennial!A26,Centennial!E6:Centennial!E26) that is the code and I need the values of the ranges to stay the same(A6,A26,E6,E26) and A13 to change as i copy and paste. How do I keep those values constant?
 

A:Excel Programming Constant Value

use a $ sign to fix the position

A6

to keep the column from changing type

$A6

to stop the row changing type

A$6

to fix both column and row

$A$6

so for your look up range

$A$6.$A$26
 

Read other 3 answers
RELEVANCY SCORE 52.8

Hiya

This is the sample code for Programming Microsoft® SQL Server™ 2000 with Microsoft Visual Basic® .NET, 0-7356-1535-7.

The book's sample files were developed and tested on a computer with Microsoft Windows 2000 Server, Microsoft SQL Server Enterprise Edition, and the Enterprise Developer Edition of Microsoft Visual Studio .NET. To use the sample files, you'll need to have Microsoft Visual Basic .NET or Microsoft Visual Studio .NET installed on your computer. In addition, you'll need Microsoft SQL Server 2000, and for some of the chapters you'll need Microsoft SQL Server 2000 updated with Web releases 1, 2, and 3. Chapter 6 of the book gives URLs for downloading Web Releases 1 and 2. Chapter 12 gives two different URLs for downloading Web release 3 -- one with the Microsoft SQL Server 2000 Web Services Toolkit and one without.

Operating System - Windows 98, NT4 & 2000, Windows Me, Win XP

http://www.microsoft.com/downloads/release.asp?ReleaseID=39542&area=search&ordinal=2

Regards

eddie
 

Read other answers
RELEVANCY SCORE 52.8

Hiya

This is the sample code for Microsoft® ASP.NET Programming with Microsoft Visual C#™ .NET Deluxe Learning Edition, 0-7356-1815-1.

System Requirements

To use the sample code files, you'll need the following:
Microsoft Windows 2000 (SP2 recommended), Microsoft Windows XP Professional, or Microsoft .NET Server
Microsoft SQL Server 2000 (Personal, Standard, or Enterprise Edition -- SP2 recommended) or the Microsoft SQL Server Desktop Engine (MSDE), included with Microsoft Visual C# .NET
Microsoft Internet Explorer 5.5 or later
Internet Information Services (IIS). To access the ASP.NET features, IIS must be installed prior to installing the .NET Framework.
The Microsoft .NET Framework Software Development Kit (Note: you don't need to install the .NET Framework SDK separately if you install Microsoft Visual Studio .NET)

Operating System - Windows 98, NT4 & 2000, Windows Me, Win XP

http://www.microsoft.com/downloads/release.asp?ReleaseID=43351&area=search&ordinal=11

Regards

eddie
 

Read other answers
RELEVANCY SCORE 52.8

Hello All techies here,

I am very much new to this forum.I am aware that it is possible to send emails from Excel using VB programming but I am very much unaware of VB programming and tricks.

I am working on a work where I need to generate automated mails from excel via outlook based on the contents of the selected cell and some logics.

Here is the URL for image that will give some rough idea.

https://fbcdn-sphotos-d-a.akamaihd.net/hphotos-ak-snc7/s480x480/391681_552548121437857_52068638_n.jpg

or
https://fbcdn-sphotos-d-a.akamaihd....480x480/391681_552548121437857_52068638_n.jpg

or

from this sheet(shown in image) I want to generate mail with logic like this :
case 1) If i click on MSS in row ABC ,click on APD and then press " @ " then it should generate a mail like below and send it to [email protected] and cc : [email protected]

subject : "card request"

mail body
**********
Hello,

My name is ABC (i.e. A2 content).
I need to have some MSS card of APD type
value 1
value 2
value 3
if above cards are not present then we can also use VD
value 4
value 5
value 6

Regards,
Shashank1320
*************

case 2 )but if I press MCS in row ABC ,APD and then press @ then it should send mail to [email protected] and cc : [email protected]

subject : card request
mail body
*****
Hello,

My name is ABC (i.e. A2 content).

I need to have some MCS card of APD type
value 1
value 2
value 3

if above cards are not present then we can ... Read more

A:Email sending from Excel programming

Welcome to the forum,
I suggest you do a search for similar posts, give or take minor differences they all come down to the same solution.
Once you've found something taka a look and try it out.
 

Read other 3 answers
RELEVANCY SCORE 52.8

Hello to the members of Tech Support. This is my first post, so thanks in advance for the help! So here is goes.

-I have 9 worksheets with approximately 77500 rows and 23 columns of data between all nine.
-At the top of the worksheet I have a table data running from E7 to N7. This data is carried over from the previous month's data.
-Column AA has a formula: =Z13+(O13-Q13)*3340.4
Where Z13 is the first row where the data begins, and Z### is where the data ends. Rows of data vary from month to month. The formula carries on such that row 14 uses the same formula but insert 14 vice 13. The number 3340.4 is the number found in cell E7 and always the multiplier in the formula.
-Column AA on worksheet 2 requires the same formula. =Z13+(O13-Q13)*4567.3. However note that the 4567.3 is the next number over in cell F7 and is now the multiplier until the last row.
-Worksheet 2 has a different number of rows (which also varies from month to month.).
-The data for each month is always found in cells E7-N7.

Worksheet 3 would use date in G7 and so on....

Any help with the coding of this Macro would be greatly appreciated.
Thanks
Can someone help me with the macro to automatically
 

Read other answers
RELEVANCY SCORE 52.4

Hiya

This is the sample code for Programming Microsoft® Windows® with Microsoft Visual Basic® .NET (Core Reference), 0-7356-1799-6.

System Requirements

To use the Companion Content you'll need to be able to compile and run Microsoft Visual Basic .NET programs. The system requirements are as follows:
Microsoft Windows NT 4.0, Microsoft Windows 2000, or Microsoft Windows XP
AND
Microsoft .NET Framework SDK -- can be downloaded from the link provided above (Minimum)
OR
Microsoft Visual Basic .NET (Preferred)
OR
Microsoft Visual Studio .NET (Best for multi-language development)
To run your Microsoft Visual Basic .NET programs on other computers requires the .NET runtime (also referred to as the .NET Framework redistributable package) to be installed on those machines. The redistributable package comes with the Microsoft .NET Framework SDK, Microsoft Visual Basic .NET, and Microsoft Visual Studio .NET. The redistributable package can be installed on the versions of Microsoft Windows listed above as well as Microsoft Windows 98 and Microsoft Windows Millennium Edition (Me).

Operating System - Windows 98, NT4 & 2000, Windows Me, Win XP

http://www.microsoft.com/downloads/release.asp?ReleaseID=40364&area=search&ordinal=3

Regards

eddie
 

Read other answers
RELEVANCY SCORE 52.4

Hiya

This is the sample code for Sample Code: Programming with Managed Extensions for Microsoft® Visual C++® .NET, 0-7356-1724-4.

System Requirements

The first five chapters of the book require only the C++ compiler (version 13). The C++ compiler supplied as part of the Microsoft .NET Framework SDK does not produce optimized code, nor does it provide extensions like the unmanaged ATL Attribute Provider, but it is a fully featured C++ compiler that can be used for both managed and unmanaged C++ development. If you want to learn about the Microsoft .NET Framework, the C++ compiler is the place to start. The link provided above will connect you to the MSDN resource page for .NET, where you can download the Microsoft .NET Framework SDK.

The last two chapters of the book use features of Microsoft Visual Studio .NET. Microsoft Visual Studio .NET includes the full optimizing C++ compiler, and it also comes with unmanaged libraries: the complete CRT library, the standard C++ library, and the combined ActiveX Template and Microsoft Foundation Class libraries, all of which you can access from .NET code.

Operating System - Windows 98, NT4 & 2000, Windows Me, Win XP

http://www.microsoft.com/downloads/release.asp?ReleaseID=40404&area=search&ordinal=1

Regards

eddie
 

Read other answers
RELEVANCY SCORE 52

hi to all,

i'm writting a marco to populate the row and col in the spreadsheet. I provide an form interface for the user to key in the data before reflect the data in the spreadsheet. my question is how do i detect the first blank row and transfer the data into that blank row in the spreadsheet.

i only have 9 columns and therefore i hardcode the colum, however, i need to find the blank rows so as to insert the data.

Cells(currentRow, 1) = txtTime.Text
Cells(currentRow, 1) = txtDate.Text

can someone tell me how to fiind the index of the rows???????

Thank in advance
 

A:excel marco programming detecting blank row

The code you have writes txtTime and txtDate to the same cell. Try:

newrow = Range("A65536").End(xlUp).Row + 1
Cells(newrow, 1) = txtTime.Text
Cells(newrow, 2) = txtDate.Text

, assuming you have at least one row already.

I couldn't tell you what data type to use for newrow. Zack could tho', he might drop by later.

HTH,
bomb
 

Read other 3 answers
RELEVANCY SCORE 52

Question to the group:
I do a lot of intensive VBA programming within MS Access (2003 and 2010) for clients and have always programmed on PC platforms within Windows 2000, XP, 7 operating systems. Everything's worked just great. No problems.
I recently purchased a MacBook Pro and had Windows 7 installed on Bootcamp. I've installed MS Office 2010 and the installations are all humming along nicely and everyone seems to be playing well together.
My problem is that my existing Excel and Access applications that use VBA code generate boatloads of errors on the Mac platform (with Windows 7 running on Bootcamp). Attempts to program anything but the simplest VBA scripts also generate errors that do not appear on the PC platforms.
My questions are: Is this a problem with References or program/computer settings? Have other programmers tried to do the same thing I am doing and had the same problems? Is the Mac system simply incompatible with a Bootcamp/Windows/Office/VBA layering? Is there a solution? I can't find any other references to this problem in the searches I have performed so far. Any input would be appreciated.
Thanks in advance.
 

Read other answers
RELEVANCY SCORE 51.6

Hi there

I found an example on the internet on how to solve a linear programming problem using Excel 2000 Solver. I have attached a workbook with all the information you will need to solve the linear programming problem. I must be doing something wrong because my solution is different from the example I found on the internet. Here is the link to the internet example

http://www.ateneonline.it/chase2e/studenti/tn/6184-7_supA.pdf
 

Read other answers
RELEVANCY SCORE 51.6

Excel 2016 Formulas is fully updated to cover all of the tips, tricks, and techniques you need to maximize the power of Excel 2016 through the use of formulas. This comprehensive Training Program explains how to create financial formulas, release the power of array formulas, develop custom worksheet functions with VBA, debug formulas, and much more. Whether you're a beginner, a power user, or somewhere in between this is your essential go-to for the latest on Excel formulas

Enrol This Course Now

PART=1
1. Workbooks (File) & Worksheet Handling
Excel Limitation

Excel Shortcut use and benefits

Excel Setting and Custom List Creation

Excel Tamplate and File Location System

Advanced Paste Special

Calulation with Paste Special

Link Data range as Picture Link

Loopkup Picture in Excel Worksheet

2. Advanced Formating for Reports and Dashboard
Formatting Triks

Date & Time For Trinks

Advanced Custom Formating (Number Format using Code)

Condtional Formatting

Advanced Conditional Formatting using Formulas

File,Sheets or Cells Protection Method

Benefits of Data Table or Cells Style

3. Data Handle and Data Analysis
Data Fill and Selection Method

Advanced Sorting and Filter Trinks

Data Summerization with Pivot Table

Calculation with Pivot table

Extranal Data Pivot Table

Relationship and Slicer in Pivot Table

Power Pivot in Excel 2016

Use SQL Query in Pivot Table

4. Page Setting and Form Creation
Drow Form in Excel Sheet

Hyperlink i... Read more

Read other answers
RELEVANCY SCORE 48.4

Firstly I am a beginner of beginners but I try to learn as much as I can.
I have put the following protection code into an Excel 2003 workbook and everything runs ok. I then opened the workbook in Excel 2007, and saved it as a .xlsm file. When I try to open the .xlsm file in Excel 2007, I get a vba Runtime error 13, "Type mismatch", and the code sticks at the first line below("If Worksheets...). I have tried everything that my limited knowledge allows, searched the web for 2 days, and still have no answers.
Any help would be greatly appreciated.

Private Sub Workbook_Open()
If Worksheets(28).[A51] > "" And Worksheets(28).[A50] = Worksheets(28).[A51] = False Then
MsgBox "Sorry, this program is not registered for this computer, please e-mail .......... for more info."
Application.DisplayAlerts = False
Application.Quit
End If
End Sub
My system specs are shown below.

Tech Support Guy System Info Utility version 1.0.0.2
OS Version: Microsoft Windows 7 Home Premium, Service Pack 1, 64 bit
Processor: Intel(R) Core(TM) i3 CPU M 380 @ 2.53GHz, Intel64 Family 6 Model 37 Stepping 5
Processor Count: 4
RAM: 2934 Mb
Graphics Card: Intel(R) HD Graphics, 1243 Mb
Hard Drives: C: Total - 290143 MB, Free - 172186 MB;
Motherboard: Dell Inc., 0WXY9J
Antivirus: avast! Antivirus, Updated and Enabled
 

A:Solved: Excel 2003 vba code not working in Excel 2007

Read other 16 answers
RELEVANCY SCORE 48.4

I'm working on a Bill of Material creation automation project that requires some expertise in VBA, and I have none.
The objective is to run a macro from an excel spreadsheet called "PGE BOM", to do the following:
1) Go to the folder "C:\Documents and Settings\Desktop\Auto Project"
2) Find all the .mdb databases in this folder
3) Find "HistoricalMaterialItemsAll" table in EACH of those databases in step 2, and import the data from the columns listed below into PGE BOM.xls's columns C through G:
DrawingNumber
ItemNumber
Quantity
PgeCode
Description
The following is a VBA code that my friend had written in Excel 2007. Unfortunately I have an older version (2000) and the code does not seem to be compatible with Excel 2000.
Sub ImportAccessData()
Stop
dPath = "C:\Documents and Settings\Desktop\Auto Project\"
sFile = "*.MDB"
strSrch = dPath & sFile
Set TargetWB = Application.ActiveWorkbook
Set TargetWS = TargetWB.ActiveSheet
sRow = 2
bFile = False
If Dir(strSrch) <> "" Then
strFlNm = Dir(strSrch)
bFile = True
End If
Do Until bFile = False
strPath = dPath & strFlNm
Call GetData(strPath)
strFlNm = Dir
If strFlNm = "" Then bFile = False
Loop
End Sub
Sub GetData(fl)
Stop
strSQL = "Select HistoricalMaterialItemsAll.* From HistoricalMaterialItemsAll"
Workbooks.OpenDatabase fl, strSQL, xlCmdTable
Set WB = Application.ActiveWorkbook
Set WS = Applicatio... Read more

A:Excel Macro - VBA code to import access data to excel

Hello, and welcome to the board!

When posting code, please use CODE tags, which extremely helps with readability.

Perhaps you could try using the code constant???...
Code:

Option Explicit

Sub ImportAccessData()
Dim dPath As String, sFile As String, strSrch As String
Dim TargetWB As Workbook, TargetWS As Worksheet
Dim sRow As Long, bFile As Boolean, strFlNm As String, strPath As String
dPath = "C:\Documents and Settings\Desktop\Auto Project\"
sFile = "*.MDB"
strSrch = dPath & sFile
sRow = 2
bFile = False
Set TargetWB = Application.ActiveWorkbook
Set TargetWS = TargetWB.ActiveSheet
If Dir(strSrch) <> "" Then
strFlNm = Dir(strSrch)
bFile = True
End If
Do Until bFile = False
strPath = dPath & strFlNm
Call GetData(strPath, TargetWB, TargetWS)
strFlNm = Dir()
If strFlNm = "" Then bFile = False
Loop
End Sub

Sub GetData(fl As String, WB As Workbook, ws As Worksheet)
Dim strSQL As String, iRow As Long, sRow As Long
strSQL = "SELECT HistoricalMaterialItemsAll.* FROM HistoricalMaterialItemsAll"
Workbooks.OpenDatabase fl, strSQL, 3 'xlCmdTable
iRow = 2
Do Until ws.Cells(iRow, 1) = ""
ws.Cells(sRow, 7) = ws.Cells(iRow, 5) 'Get the Description
iRow = iRow + 1
sRow = sRow + 1
Loop
Application.DisplayAlerts = False
WB.... Read more

Read other 1 answers
RELEVANCY SCORE 42.8

Hiya

Visual Studio Tools for the Microsoft Office System can help you take advantage of the productivity enhancements in the Microsoft .NET Framework to extend Microsoft Office Word 2003 and Microsoft Office Excel 2003 using Visual Basic .NET and Visual C#. The tools include new Visual Studio .NET projects for creating code behind Word documents, Word templates, and Excel workbooks.
System Requirements
Supported Operating Systems: TabletPC, Windows 2000, Windows 2000 Service Pack 2, Windows 2000 Service Pack 3, Windows ME, Windows NT, Windows Server 2003, Windows XP, Windows XP 64-bit

.NET Framework
Office 2003

http://www.microsoft.com/downloads/...5f-c764-4cc3-a5f3-48c9103f6b2f&DisplayLang=en

Regards

eddie
 

Read other answers
RELEVANCY SCORE 42.4

Hi, I'm new to this forum.
I was looking for different dialog options and VBA code for my Excel sheets when I came upon the Printer.Dlg code where you can choose the sheet you want to print.
I have altered the code in such a manner that you can use the checkboxes to select one or more items from a specific range in any sheet and whereever it is located.
If there is anyone intersetd I will post it.
What is does is you call the function giving the sheetname, column letter, starting rownumber and endding rownumber:
Call SelectFromList("Sheet3", "D" , 5, 25)
This will result in a Dialog screen with a list of 20 values in the range which you can select and then write that part of the code you need that does something with this.
The code allows any number of rows, The max per dialog screen is 200 spread over 4 columns of 50.
It was fun to do.
I am beginning to like VBA routines in my Excel sheets thus permitting users to enter only that data which is requested of making only the permitted selections.

Thanks for the attention.

Cheers,

Hans
 

A:VBA code for Excel

Hi, since you started this, please do post your code so if someone else comes looking for such a solution they can find it.
I would suggest that you check out this site also - it's a great place for coders and several of the other who post here regularly also haunt it (in fact, one of the people who manages it, who goes by firefytr, is also a regular here)
http://www.vbaexpress.com/forum/index.php
 

Read other 2 answers
RELEVANCY SCORE 42.4

Good Morning.
I'm trying to write VB code for Excel in a workbook that tracks vacation time accrued and used. Each line is for an employee, with hire date, latest anniversary date, vacation due (from a lookup table based on years worked), vacation time used, and balance (and a couple other insignificant items).

I want the user to select the row for an employee who has passed his anniversary date and execute code to do the following:

Erase all the vacation time recorded accross the column (month by month), and roll the anniversary date one year forward. This is the part I'm having trouble with. What would be the code to take a date and add one year. This is sometimes 365 days and sometimes 366, and and it isn't based on "now", its based on the span of time from the current anniversary to the next anniversary that I'm updating to. I've tried to build some tables, but there are too many variations.

Any ideas?
Thanks.
 

A:Excel VB Code

Read other 6 answers
RELEVANCY SCORE 42.4

Code:
Sub Data()
'
' SummarySheet Macro
' Macro recorded 2/12/2009
' Create links to all sheets and show totals
'
Dim WS As Worksheet
Dim wsANm As String
Dim wsA As Worksheet
Dim r As Integer
Dim MyTot1 As Variant
Dim MyTot2 As Variant
Dim MyTot3 As Variant
Dim MyTot4 As Variant
Dim MyTot5 As Variant
Dim MyTot6 As Variant
Dim MyTot7 As Variant
Dim MyTot8 As Variant
Dim MyTot9 As Variant
' Cell that has the totals on each sheet
Set MyTot1 = Range("c7")
Set MyTot2 = Range("c6")
Set MyTot3 = Range("C11")
Set MyTot4 = Range("e11")
Set MyTot5 = Range("g11")
Set MyTot6 = Range("B4")
Set MyTot7 = Range("D8")
Set MyTot8 = Range("K2")
Set MyTot9 = Range("L2")

'
Set wsA = Worksheets.Add(Before:=Worksheets(1))
MyTot1 = MyTot1.Address
MyTot2 = MyTot2.Address
MyTot3 = MyTot3.Address
MyTot4 = MyTot4.Address
MyTot5 = MyTot5.Address
MyTot6 = MyTot6.Address
MyTot7 = MyTot7.Address
MyTot8 = MyTot8.Address
MyTot9 = MyTot9.Address
wsANm = wsA.Name
On Error Resume Next
wsA.Name = "Data"
NoName: If Err.Number = 1004 Then
Application.DisplayAlerts = False
Sheets("Data").Cells.Clear
'Sheets.Add.Name = "Data"
'Application.DisplayAlerts = True
'wsA.Name = "Data"
'End If
'If wsA.Name = wsANm Then GoTo NoName
On Error GoTo 0
'
r = wsA.Range("B65536").End(xlUp).Row + 1
wsA.Cells(r + -1, 1).Value = "Date#"
wsA.Cells(r + -1, 1).Font.Italic = True
wsA.Cells(r... Read more

Read other answers
RELEVANCY SCORE 42.4

I would like to know or get help with the VBA coding to do the following if possible, I have an excel sheet with file links (link in column (I) link text in column (J)) there are e-mail addresses in Column (H) and required dates in column (D).
I would like to know if the dates can be compared (today) for out of date occurrences and dates approaching 31 days of the date in column (D) (older than (=TODAY()+31)) a possibility would be to ask for user defined input on the dates after today (31) in the example above.
In this event the sheet could be filtered to show all columns and only those rows with the dates meeting the criteria, which would then raise an email (Outlook) to be viewed prior to sending and attach the files (column (I) or (J))
One e-mail to one recipient but multiple files if there are any?
From: (me)
To: (I will add a column with the recipient’s name) let’s say (L)
CC : (someone)
BCC: (someone else)
Subject: “text” + cells in column (C) relative to all the date occurrences to the recipient in column (H)

Body text
((Name in Column (L))

Text 1
Text 2
Text 3

A sample file is attached with arbitrary information

Many thanks in appreciation
 

A:Excel Code Help

I note a few views, and can appreciate its a lengthily request, I am trying to refresh my coding as it has been nearing 20 years since doing anything.

But I have made a start; Button calls on macro to get the date and append it with user input to advance the filter criteria, and then creates a new sheet with the date of the filter.
Code:
Sub In_Progress()
Dim vData
Dim firstDate As Date, secondDate As Date
Dim ActNm As String
'Dim LastColumn As Long
On Error Resume Next
Application.DisplayAlerts = False
vData = Application.InputBox _
(Prompt:="Please select a single cell housing the number, " _
& "or enter the number directly.", _
Title:="Days from Today", Type:=1 + 8)
On Error GoTo 0
Application.DisplayAlerts = True
If IsNumeric(vData) And vData <> 0 Then

firstDate = DateValue(Now)
secondDate = DateAdd("d", vData, firstDate)
'test date increment by user input
MsgBox secondDate

With ActiveWorkbook.Sheets

.Add after:=Worksheets("ALL - cals")

End With

ActNm = ActiveSheet.Name

On Error Resume Next

ActiveSheet.Name = "due - cals " & Format(secondDate, "mm-dd-yy")

NoName: If Err.Number = 1004 Then ActiveSheet.Name = InputBox("Give name... Read more

Read other 3 answers
RELEVANCY SCORE 42

Hiya

Learn about COM interoperability between Microsoft C# and large, complex COM servers. This article shows how to prepare the Office XP COM objects and how to use them in a C# program, and offers some tips on understanding why things have to be done in certain ways.

System Requirements
Supported Operating Systems: Windows XP

To run the sample, you will need the following software installed on your computer:

Microsoft Windows® XP or Microsoft Windows 2000 and related service packs (SPs)
Microsoft Office XP and related SPs
Microsoft .NET Framework and related SPs
Microsoft Office XP Primary Interop Assemblies (PIAs)
Microsoft Visual Studio® .NET
http://www.microsoft.com/downloads/...0c-d2f3-48bb-8e76-7c8892b5ed95&DisplayLang=en

Regards

eddie
 

Read other answers
RELEVANCY SCORE 42

I'm writing a macro in Excel 2007 and would like the macro to pause for user input and then search for the word input by the user.

Any suggestions would be appreciated.

Thanks.
 

A:Excel VBA Code Question

The easiest way to do this is to prompt the user with an inputbox. Can you post your existing macro code for us to look at?

Regards,
Rollin
 

Read other 2 answers
RELEVANCY SCORE 42

Hi I am getting weird exception and I dont understand why, I been struggling and no luck

When I load (download) an excel file with macro from a server to my desktop and open it
I get the below error message:

Run-time error '1004':
Unable to get the CheckBoxes property of the Worksheet clas

I noticed if I open and click save, close and open again the error is not there anymore.

Why is the error gone? I am not changing anything in the file, just save and open again.

Thank you for your help
 

A:Solved: Excel VBA code

It may be that the code is refering to a named object, which can only be in such state when saved rather than being opened from temp location.
 

Read other 1 answers
RELEVANCY SCORE 42

This is the what I want to happen...

ActiveWorkbook.SaveAs Filename:=Range("B1").Value
The code will read the value in B1 and make that the saveas file name.

How do I do this??
Thank you.
 

A:how to insert VBA code with Excel

Tools -- Macro -- Record. Make sure you store in This Workbook. Click OK. Stop Recording (click the black square on Stop Recording toolbar).

Press ALT+F8. Highlight the macro name, click Edit.

Replace everything between the "Sub ..." and "End Sub" lines with your code. Close the Visual Basic Editor.

NB - your code will crash if whatever's in B1 isn't a valid filename.

Rgds,
Andy
 

Read other 1 answers
RELEVANCY SCORE 42

I have several checkboxes on Sheet1 that populates Sheet2; if the checkbox = true. Depending on the “text” of A# on Sheet2, then a listbox will populate cell D# on Sheet 2.

The problem I’m having is that I want the screen to stay on Sheet1 as the user clicks on the checkboxes. With the code I’m using, I’m receiving errors.

Private Sub CheckBox36_Click()
If CheckBox36.Value = True Then
Worksheets("Sheet2").Range("A6").Value = "Air Emissions"
Worksheets("Sheet2").Range("B6").Value = "Point Source Air Emission"
Worksheets("Sheet2").Range("C6").Value = "NOX"
Else
Worksheets("Sheet2").Range("B6").ClearContents
End If
If Worksheets("Sheet2").Range("A6").Value = "Air Emissions" Then
Worksheets("Sheet2").Range("D6").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=AirEmissionsI"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End
End If
End Sub

Using the code above, I receive the "Select Method of Range Class Failed"; debug in red

So I tried this below (changes in blue)

Private Sub CheckBox9_Click()
Dim n As Range
Set n = Sheets("Sheet2").[d5]
If CheckBo... Read more

A:Excel 2002 VB Code Help

I don't do that much programming in Excel, but I'm really familiar with VBA, so I'll take a shot. Seems like you have two possibilities:

1. turn off Echo (the screen updater, check out Excel help if you don't know it), and make Sheet2 the active sheet, carry out the stuff, and make Sheet1 the active sheet, and turn Echo back on. The user will see nothing. I'm not sure if your problem is caused by the fact that the sheet is inactive.

2. name the range you're trying to activate, in your second code example. Then you should be able to set your variable to Worksheets("Sheet2").Ranges("NameOfYourRangeHere"), without too much trouble.

If none of this works, and no one else has a better idea, I can dig into my Excel code archives and see what I can come up with.

kls is gonna rock you. hehe.
 

Read other 3 answers
RELEVANCY SCORE 42

i have studied VB back in university time like 8 years ago! now im getting hints and tips from the net to find my way: i have found the below code and it works perfectly now i need to add another code to copy anything inside the range of sheet 1 to sheet2 and sheet 3 with timestamp whenever i run the macros (like activity log), pleeeease!

Sub Mail_Range_Outlook_Body()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2010
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set rng = Nothing
On Error Resume Next
Set rng = Sheets("").Range("B2:J5").SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If rng Is Nothing Then
MsgBox "The selection is not a range or the sheet is protected" & _
vbNewLine & "please correct and try again.", vbOKOnly
Exit Sub
End If

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = ""
.HTMLBody = RangetoHTML(rng)
.Display 'or use .Send
End With
On Error GoTo 0

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing

End Sub
 

A:Excel Macros Code

am i at the right category to post that? sorry im new
 

Read other 3 answers
RELEVANCY SCORE 42

Good Morning
I'm trying to build a nice data entry form in Excel. Has 5 required fields, the first 2 of which should default to name and todays date - which is at the top of the spreadsheet. I have 5 buttons: Next, Previous, Add, Delete, Close. I'm working on the code for those. But in the meantime, I'm hung up on the following problem:

Some code that I have written keeps taking Name out of the spreadsheet cell A1. I can understand how that can happen when I run the form's code. But now when the form is closed, and I try to type the name back into A1, it will not stay. When I hit enter, it just disappears, not even in the formula bar. I've checked the formatting and nothing is odd there. If I insert a new row 1, the old row (now 2) works, and the new row 1 still has disappearing text. So something in my code is telling it to blank out cell A1. I don't understand how that can happen if I don't even run the code?

The following is all the code behind the form. The only other code in the project is a button click on sheet one. The only code directly referring to Cell A1, is at the very bottom with the A1 reference bolded. Thanks for your help.
-----------------------------------------------------------

Dim CurrentRow As Long

Private Sub cmdAdd_Click()
' Save form contents before changing rows:
SaveRow
' If list is empty start in row 5:
If Cells(5, 1).Value = "" Then
CurrentRow = 1
' Set current row to first empty r... Read more

A:Excel Code for a form

I have tried recreating the problem that you describe but with no success.
What version of Excel are you using?
Can you post the file? It would save others having to recreate the form.

I think I found a bug in your code.

In cmdAdd_Click and UserForm_Activate I think that the following line:
CurrentRow = 1
needs to be changed to
CurrentRow = 5
given your comments in the code.

Is there any other information that you can give?
 

Read other 3 answers
RELEVANCY SCORE 42

Hi,

I'm trying to set up a Question and Answer form where different things occur depending on what the answer is. I have two buttons - "Yes" and "No".
My sheets are "Guide", "Segments" and "Receipt".

The questions are on "Guide".

If "Yes" is selected I want Cell E3 on "Guide" to be copied to Cell C31 on "Receipt"

If "No" is selected I want Cell F3 on "Guide" to be copied on Cell C31 on "Receipt" and Cell A3 on "Guide" to be copied to Cell H4 on "Segments".

There are mulitple questions, but once I have the basics of how to do this I am happy that I could replicate this for the rest of my questions.

Many thanks.
 

A:Excel Button Code

Hi,
Will it always be Cell E3 on Guide -> C31 in Receipt and F3 and the other ones too?

And not less important, which version of Excel are you using?
 

Read other 2 answers
RELEVANCY SCORE 42

I am trying to set up a Excel file which will prompt the operator enter the lowest and higest cell row number which is needed and then will perform several operations using the data within the slected region.

example of what I want: I have 1 columns and 100 rows and I only need to take the average of each column on row 40 to row 50.

So the program ask me to enter in the lowest row, and the hightest row and takes an average in that region.

I now want to analys rows 60 to 70 with out changing the code.

How would I go about writing such a file in excel
 

A:Excel code question

Read other 7 answers
RELEVANCY SCORE 42

hi, below is the vb code added to most of the worksheets in a work book; this code is not working now;
history...this code was working...then i realized that i could have the same code apply to all worksheets in the work book, so i deleted the recently applied code for each worksheet and implemented the code for the work book level; unfortunately, there were a few worksheets at the end of the workbook that were not the same format as the others and this code afftected results in those worksheets; after realizing this, i deleted the code that worked at the workbook level and then added the origional code back to each worksheet. what do i need to do to make this code work now?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("c4").Value < Range("c19").Value Then
Range("c4").Value = Range("c19").Value
End If
End Sub
 

A:excel 2010-vb code help

Hi there,

The code below when it's in a VBAProject module will go through all the worksheets and test C4 agains C19
Code:

Sub ChangeC4()
Dim ws as Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Range("c4").Value < ws.Range("c19").Value Then
ws.Range("c4").Value = ws.Range("c19").Value
End If
Next ws
End Sub

However, if not all worksheets are the saem format and this does not apply, then you will need to include a test verifying that the worksheet is one of the range where change applies to

The If statement will have to come in the redline below

and the End If statement at the second red line of code
Code:

Sub ChangeC4()
Dim ws as Worksheet
For Each ws In ThisWorkbook.Worksheets
[COLOR="Red"]< Here must come an If statement testing the worksheet>[/COLOR]
If ws.Range("c4").Value < ws.Range("c19").Value Then
ws.Range("c4").Value = ws.Range("c19").Value
End If
[COLOR="red"]< And here the end if >[/COLOR]
Next ws
End Sub

It's bed-time here so I'm off-line
 

Read other 1 answers
RELEVANCY SCORE 42

Can anyone help with why this error is appearing for the row of code below

Run-time erroe '424': Object required

ws.Cells(iRow, 1).Value = Me.txtJobName.Value

I have posted my whole code below what i am doing is using a userform to add new job name and job number to a separate workbook
Code:

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim wb As Workbook
Set wb = Workbooks("Defined Name Lists.xls")
wb.Activate
Worksheets("JobName").Range("A2").Value = txtJobName.Text
'find first empty row in database

'check for a JobName
If Trim(Me.txtJobName.Value) = "" Then
Me.txtJobName.SetFocus
MsgBox "Please enter a Job Name"
Exit Sub

End If
'check for a JobNo
If Trim(Me.txtJobNo.Value) = "" Then
Me.txtJobNo.SetFocus
MsgBox "Please enter a Job No"
Exit Sub

End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtJobName.Value
ws.Cells(iRow, 2).Value = Me.txtJobNo.Value


If Me.txtJobNo.Value <> "" Then
Dim wbOutput As Workbook
Set wbOutput = Workbooks.Add
With wbOutput
.Title = "Job Number "
.Subject = "Job Number created " & Date
.Author = "Script generated"
.SaveAs Filename:=mydir & "\" & Me.txtJobNo.Value
End With
End If

'clear the data
Me.txtJobName.Value = ""
Me.txtJobNo.Value = ""
Me.txtJobNo.SetFocus
Exit Sub

End Sub
... Read more

A:Excel Code Problem

You have not Dim or Set the variable ws
 

Read other 3 answers
RELEVANCY SCORE 42

Sub GetData()
Application.ScreenUpdating = False
If ThisWorkbook.Sheets("Raw Data").Range("DD").Value = "YesData" Then
Worksheets("DD").Range("A1:Y100").Value = Worksheets("Raw Data").Range("A1:Y34").Value
End If

In the above code I am trying to populate another sheet "DD" with specific information from "Raw Data". In the If statement I want the data populated to only retrieve data rows from "RawData" that contains the word "YesData" in the D column.

Please help.
 

Read other answers
RELEVANCY SCORE 42

I have developed a small database in excel which utilizes a UserForm to collect information for new database entries. I have only limited understanding of VBA programming, so I need a little help on the code to perform the following function.

After I type in all the required fields in my UserForm, what code do I need to insert in order for the new entries to be inserted in the first empty row of the database? I know how to set the value of a cell to the data in the UserForm, but I'm not sure how to make the computer start from the top of the database and move downward until it finds an empty row, and then insert the entered data into the row.

Any help would be greatly appreciated!

Thanks
 

A:Solved: Excel VBA Code Help

Read other 9 answers
RELEVANCY SCORE 42

Hello

I made a VBA code for my excel to automatically send email and it is giving error when I run it. Please can any one please check what am i doing wrong here.

Following is my VB code

Sub Mail_with_outlook()
Dim OutApp As Object
Dim OutMail As Object
Dim strto As String, strcc As String, strbcc As String
Dim strsub As String, strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strto = Cells(FormulaCell.Row, "M").Value
strcc = ""
strbcc = ""
strsub = "Your subject"
strbody = "Hi " & Cells(FormulaCell.Row, "L").Value & vbNewLine & vbNewLine & _
"Your total of this week is : " & Cells(FormulaCell.Row, "D").Value & _
vbNewLine & vbNewLine & "Good job"
With OutMail
.To = strto
.CC = strcc
.BCC = strbcc
.Subject = strsub
.Body = strbody
.Display ' or use .Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Thanks in Advance
 

A:Please help in Excel VBA code problem

Problem coming in this coding is:

strto = Cells(FormulaCell.Row, "M").Value
and I dont know why.

Please can anyone help
 

Read other 2 answers