Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Enter data to cell in Excel using VBA?

Hi,

I Hope some can help me with this, basically I have written a simple procedure to add 3 values from a string and now want the answer to be entered in another cell
e.g

A10 = 234
adding the 3 values I have myVar = 9
how do I then place this value in cell B10????

Hope this makes sense.

TonyJ


Post your answer or comment

comments powered by Disqus

Related Results

  1. Can we alter somethings in SPEAK CELLS & VOICE NOTES in Excel using VBA?
  2. Interrupting long calculations in Excel? Hi all, I am having a verylong calculation in Excel using VBA. Of course I know I can hit ESC orCTRL+BREAK to interrupt the VBA program. But I found due to too long runningprocess, the Excel window stop to
  3. Input number in text box, dynamical generate text boxes related to cells in excel
  4. Link cells in Access to Excel using VLOOKUP
  5. Using VBA in Excel to Email
  6. Repainting a cell in Excel using VBA
  7. double click option in listbox outputs to cell in excel 97 !
  8. Protect The Selected Cells In Vba
  9. Creating/manipulating form controls placed on a spreadsheet in excel using vba
  10. Convert excel data to 2-D array using macro
  11. Opening Excel using VBA
  12. How so I navigate in Excel with arrow keys?
  13. Copy data from one sheet to first empty cell in another using VBA button
  14. Transfer data from cells in Excel to Word
  15. how to new(Updated) Sql Data import in excel using vba
  16. Copy data in excel through vba / hide formulas
  17. How can I move from one desired cell to another in Excel using ENT
  18. Importing specific text and csv files into separate worksheets in Excel using VBA
  19. Copying visible cells in Excel using VBA
  20. Export data from excel using VBA
  21. Copy data from cell in one worksheet, add text, and paste to cell in another sheet
  22. How to convert Columnar Data Range in UPPERCASE using VBA and not formulas?
  23. VBA program for data entry - find the matching data to update
  24. How to program dependent list in Excel using VBA?
Can we alter somethings in SPEAK CELLS & VOICE NOTES in Excel using VBA?

Dear Forum,

I just recently learnt about the SPEAK CELLS & VOICE NOTE which is present in excel and can be put to best use especially when you want to explain a lot of matter to the end-user but not only that there's this feature SPEAK CELLS which just reads out the contents directly on entry in any cell on ENabling this feature..

I want to know first can we change the pace of the SPEECH , mean reduce it atleast by a good 25-30% to make it more easier to recall as mostly at work you cannot have the SPEAKERS on it not always professional to have your headsets on in front of an Typical OLD SCHOOL BOSS who is more keen ablut just other things than just work..

The other thing is that it read numbers like for example:

If the Number in a cell is 123 which will be read as One Hundred and Twenty Three with all the Tens THousands and Units Place..

However for faster usage with a lower pace can we not have it read just like One Two THree so that the Data Entry Person will find it easier to track the errors if any..

P.S. DOes anyone have any good examples of work done using VOICE NOTES, please can someone share the same..

Warm REgards
e4excel

Interrupting long calculations in Excel?

Hi all,

I am having a very long calculation in Excel using VBA.

Of course I know I can hit ESC or CTRL+BREAK to interrupt the VBA
program.

But I found due to too long running process, the Excel window stop to
respond for very long time,

besically it's like "not responding" situation with "not responding"
on the windows title bar...

It doesn't seem to accept any more key strokes, not to say ESC or CTRL
+BREAK.

What can I do to rein/stop my program?

Thanks

Hi

I realy breaking my head about this problem.
I need to make a userform, that makes the next things:
1.) for a entried a number in a text box,
2.) when i click ok, it should open a next form, with so many text boxes i have declared in the first text box
3.) these new text boxes, need to be related to cells in excel, for example when i entrie in the first text box a number, it should be input in cell A2, and so on...

thanks

I would like to link data in Access to cells in Excel using VLOOKUP. How
would this be done?

Im trying to figure out how to email in excel using VBA. I can get it to work but what im using is limited. I would like to email an attachment to a large amount of people, have a CC line, subject, and body. The email addresses change each time i run my program. Some are added and some are dropped so the people i email this spreadsheet to change. Is there anyway to create a string of addresses and use that string on the To: line somehow?

Hello,
I am writing a code in excel VBA to generate a report.I am totally new to VBA.Here's my code
Private Sub CommandButton1_Click() 
Dim a1 As Integer 
Dim d1 As Integer 
Dim r1 As Integer 
a1 = Sheet1.Cells(2, 3) 

d1 = a1  8 
r1 = a1 Mod 8 

If d1 >= 5 Then 

Sheet1.Cells.Range("A20:E20").Interior.Color = vbBlack 

Else 

For cnt = 1 To d1 

Sheet1.Cells(20, cnt).Interior.Color = vbBlack 

Next cnt 

If r1 <> 0 Then 

Sheet1.Cells(20, cnt).Interior.Color = vbBlack 

Sheet1.Cells(20, cnt).Font.Color = vbWhite 

Sheet1.Cells(20, cnt) = (8 - r1) 

End If 

End If 
End Sub
..
The basic idea is to colour cells after the click of command button based on the value in Cells(2,3).Cells get coloured based on multiples of 8. The problem that I am facing is that the cells retain the colour permanently.
For example based on the value in Cells(2,3) if 5 cells get coloured ,they remain there permanently.When I change the value in cells(2,3) and click the command button repainting of cells does not take place.. Can anyone please guide me regarding this??

Thanks

Hello,

First off... The solution has to work in excel 97 !

I am having a little trouble trying to figure this out.
I have been able to get a simple listbox, to get a range of data from
another worksheet in the same workbook, and it works just fine,
using this following code:

*********************************

Private Sub CommandButton1_Click()
Dim ListItems As Variant, i As Integer
With Me.ComboBox1
.Clear ' remove existing entries from the listbox
Application.ScreenUpdating = False
ListItems = Worksheets("data").Range("A2:A8").Value
Application.ScreenUpdating = True
ListItems = Application.WorksheetFunction.Transpose(ListItems)
' convert values to a vertical array
For i = 1 To UBound(ListItems)
.AddItem ListItems(i) ' populate the listbox
Next i
.ListIndex = -1 ' no items selected, set to 0 to select the first item
End With

*********************************

However, my issue where I'm stumped, is if I could double click on a
single option and it would output that same option to a specific cell in the
same worksheet as the listbox ?

Thank you VERY much in advance,

spyrule.

Hi,
Can one help me “How to protect the selected cells in Excel using VBA?”

Thanks,

Krishna

Hi,
I need to create/access the objects that excel creates when you place a
control on a spreadsheet in excel using the form toolbar. I can't find
a reference to the controls in vba anywhere and the only control
references I have been able to find pertain strictly to "userform1" or
some other variant of a popup form. I need to be able to create a form
directly on the spreadsheet or at least be able to access the controls
I have manually created in excel.
cheers,
-JPN

pls how do i convert excel sheet data to 2-D array using VBA macro. i intend
filtering and extracting info from this array. thanks.

Hi there folks,

I know it's possible to open another document in Excel using VBA coding however is it possible to open a new Excel program (as you would if you went to Start>All Programs>Microsoft Office>Excel) and then open your new document in that?

The reason I ask is that I have two programs, both of which work fine in their own Excel program (even when open together on the same PC) however they become tempramental when open in the same copy of Excel. And I need one to open the other!

Thanks in advance,

Andy

I'm trying to move from cell to cell in Excel using the arrow keys. When I
use these keys, the page moves as a whole. In the past we have been able to
move from cell to cell, so I'm assuming we touched a wrong button or two.

Hello all,

I hope that you can help me with the below issue:

I have 2 sheets, the first sheet is a cover sheet where users can manually enter data.
The 2nd sheet is a database which should contain all data entered by the user in the cover sheet.

The data that was manually entered into sheet 1 should be copied and pasted into the second sheet (=DATABASE) with a button click.

I have already found a way to identify the first empty cell in the database (as the user will use this button regularly, the second sheet will constantly increase in size so the first empty cell will change each time data is input and transferred).

The code is as follows:


	VB:
	
 CommandButton3_Click() 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

	VB:
	
 Roman][SIZE=3] [/SIZE][/FONT][/B] 
[B][SIZE=3][FONT=Times New Roman]Sheets("Database").[B45] = Sheets("Cover page").[J46][/FONT][/SIZE][/B] 
[B][FONT=Times New Roman][SIZE=3] [/SIZE][/FONT][/B] 
[B][SIZE=3][FONT=Times New Roman]End Sub[/FONT][/SIZE][/B] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


The [B46] in the Database sheet needs to be substituted with a cell that contains the reference (e.g. B46) to the first empty cell. The cell which contains the information where to start copying (in this case the information is “B46”) can be found in the cover sheet. (e.g. it could be cell A1 in the cover sheet / à cell A1 of the cover sheet has the value “B46”. After input was done in “B46” A1 will automatically show “B47”. (This feature has already been implemented without VBA)

For example: the user enters a name “Markus” in cell J46 in the cover sheet
This name needs to be transferred into the first empty cell in the second sheet
The first empty cell in the second sheet is B46
There is a reference to this empty cell in the cover sheet (cell A1)

First of all I want the data (“Markus”) entered into cell J46 in the cover sheet in order to be transferred to cell B46 in the second sheet.
Then I want the workbook to recognise that cell B46 in the second sheet is no longer empty and that the next empty cell is now B47 – this cell reference should now appear in cell A1 in the first sheet. (I have already worked out how to do this step without using VBA – simply take this as a given).
Then when the user enters a new name “Marie” in cell J46 in the first sheet and clicks the button, this data is transferred to cell B47 in the second sheet as this is the ‘new’ first empty cell in the second sheet

I hope that this explanation is clear and that someone is able to help resolve this issue.

Many thanks in advance.

Kind Regards,

VRF

I have an Excel workbook that contains multiple sheets. I am using the workbook for calculating a quote. I would like to take some of the data from Excel and paste it to a Word document so that I can send a professional looking quote to the customer. I would be copying some groups of cells and also individual cells from 2 of the sheets in Excel to one Word document.

What is the best way to do this? It seems like this could be done with Mail Merge or some macros/VBA code. I would like this to be fully automated so that I just click on a button in the Excel and it would open Word and the Word document and transfer everything from Excel into Word. Thanks for any help you can offer.

Hi,

first day i import ms sql server data using excel vba(ADO)

i change a data in excel.

second i want to import new records.

eg:

first 150 records i import in excel 2007.

second day new records add in sql server 25 records. Total 175 records.

How to imports new records in excel vba.

I am new to this forum. First of all i wish to thank the creators of this forum. The forum can help many who are interested to learn excel.
MY QUERY: I am working 4 sheets: sheet1: master_sheet sheet 2: A Sheet 3:B Sheet 4:C

Sheet 1 Name: Chk_ List (Master Sheet). This has the 4 coloumns and 35 rows
A1: A35 has description C1:C35 has description
B1:B35 data to be entered by the users D1:D35 data to be entered by the users

Now this data has to be copied partially in sheet 2,3,4

For example rows b1-b10 from master sheet in sheet 2--
Rows b11-30 from master sheet in sheet 3
Rows B30-B35 & D1-D35 from master sheet
desc in rows a and c is already done when sheet ,2,3,4 are created

Users will have their own files on their desktop or a common drive and they will copy and paste the values from B and D Rows in sheet 2,3,4 to a particular application which the company uses commonly.

Main objective is to enter data one time in chk list and copy it in the required places on the office software. Sheet 2, 3,4 represents the same description which we use in our office software. Basically i am taking the help of excel to fill the data thorough copy (ctrl+c) from sheets 2,3,4 in our office software/application and hide any formulas which are present in the cell. Formulas like for addtion in two rows into 1 in the next sheet, concatenate text in tow three rows into one in the next sheet.

Can i do this with out vba code where i can hide the formulas and users can just copy the results from sheet 2,3,4 in to the required application

Sorry if the query was too long . Just want to query what i wanted clearly

You can email me in case of any further queries, hints, tips,answer MY Email id is : grkchakri@gmail.com .------ Many Thanx

hello, I am working on an extremely important excel 2002 worksheet and am
using only 3 cells. The remaining cells are locked so they cannot be modified
by any third party. Lets say I am inputing data only in cells D8, M7 and M9.
I always start inputing data at M7, then M9 and finishing with D8. As soon as
I finish with M7 and pressing the ENTER button I would like to be directly
transfered to cell M9 and next to D8. Is this possible?
All searches I have made have left me unsatisfied.

many thanks

Hi All,

Firstly, I'm a beginner at VBA but I seem to get by on integrating already made codes and getting things to work at the moment.

I have a specific solution I require but haven't managed to figure out exactly how to do this effectively - it would be great if you could help?

Requirements as follows:

I have a single Excel workbook which will be used by several users/machines and I want to have a macro which:
1 - Asks user where the import files are located in a browser type window (all will be in same folder so this only needs to be done once, but the overall location of this folder may change depending on the user hence the need to ask this)

================
Another option would be to encourage the user to always keep the import folder in the same directory where the Excel file is saved but then I would prefer the code to link to /IMPORT from the relative Excel saved location rather than C/Username/Documents/IMPORT for example as multiple users will be working on this s/sheet.
================

2 - Import each specific file to the correct worksheet (detailed below), clearing existing data and pasting the new import data from Cell D5 onwards (I have data which cannot be removed in columns A-C and rows 1-4)

3 - Save the workbook.

To clarify, I want to import the following files into the worksheets indicated. The filenames will not change and they all be located in the same folder.
Textfile1.txt --> Worksheet1
Textfile2.txt --> Worksheet2
Textfile3.txt --> Worksheet3
CSVFile1.csv --> Worksheet4
CSVFile2.csv --> Worksheet5
CSVFile3.csv --> Worksheet6

I have the code for choosing the folder, but this code then imports all text files contained within this folder to 1 worksheet. I want to extend the ability of this to work for the above.

Any help on this would be much appreciated.

Thanks,

Rav

p.s. this isn't my own code, it was posted from someone else on here (http://www.ozgrid.com/forum/showthre...t=78498&page=1)


	VB:
	
 GetFiles_Click() 
    test 
End Sub 
 
Function GetFolder() As String 
    Dim fldr As FileDialog 
    Dim sItem As String 
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker) 
    With fldr 
        .Title = "Select a Folder" 
        .AllowMultiSelect = False 
        .InitialFileName = Application.DefaultFilePath 
        If .Show  -1 Then Goto NextCode 
        sItem = .SelectedItems(1) 
    End With 
NextCode: 
    GetFolder = sItem 
    Set fldr = Nothing 
End Function 
Sub test() 
    Dim myDir As String, fn As String, ff As Integer, txt As String, a() 
    Dim x, i As Long, n As Long, b(), t As Long 
    myDir = GetFolder() 
    fn = Dir(myDir & "*.gpc") 
    Do While fn  "" 
        ff = FreeFile 
        Open myDir & "" & fn For Input As #ff 
        Do While Not EOF(ff) 
            Line Input #ff, txt 
            x =  Split(txt, ",") 
            n = n + 1 
            Redim Preserve a(1 To n) 
            a(n) = x 
        Loop 
        Close #ff 
        With ThisWorkbook. Sheets(1) 
            . Cells(t + 2, 3).Value = fn 
            For i = 1 To n 
                .Cells(i + t + 1, 4).Resize(, UBound(a(i)) + 1).Value = a(i) 
            Next 
        End With 
        Erase a: t = t + n + 0: n = 0 
        fn = Dir() 
    Loop 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have a macro where I'm using an autofilter. I use:
'Range(Selection, Selection.End(xlDown)).Select and it works great, except for when it can't find the condition.

If I"m looking for 'Sugar' in the filter and 'Sugar' exists in the data set, the filter pulls all sugar items, copies this data and puts it in another workbood. I use the xlDown and it works. If 'Sugar' does not exist then the filter shows nothing (which is correct) but I want the macro to know that if 'Sugar' doesn't exist then It doesn't need to copy the data into another workbook.

If filter shows nothing then perform action item 1,2,3 and then end. If filter shows items, then do 1,2,3,4,5,6,7, and then end.

With VBA code, how can I check if there is data in the visible cells and if not, then don't copy? (still want to do some steps, but skip steps 4,5,6,7)

I

I have set up a button on excel so that when pressed it opens another
application and performs some actions to obtain some data. I used VBA record
to copy my actions in the other application and used preset figures to use
for inputting. However, I now need to know how to instead use figures I will
be inputting in cells in excel. any help would be greatly appreciated.

thanks

Hello all. I have the following code in another workbook that is used to populate a cell on the same sheet based on input to cells in column 'A'.

Is it possible to modify this for the attached workbook to select a cell with data (numbers) on the Input Data sheet in column 'E', add text to the beginning, ('CG' in this case), and paste the result to the Import Template in the corresponding cell of column 'A'? I currently have a formula copied to dozens of cells in 'A' but since the number of rows for the Input Template is variable, there are usually cells in 'A' that contain CG but no corresponding data in the rest of the row.

If there is a better way to code my request, that would be good as well.

Private Sub Worksheet_Change(ByVal Target As Range)

'VBA code to auto populate different protected cells

'ActiveSheet.Protect , UserInterfaceOnly:=True

'Populates Date Added field when Unit field is entered

    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        If Cells(Target.row + 1, "C").Value <> "" Then
            If MsgBox("Order Date already exists. Update the date to today?", vbYesNo + vbQuestion) = vbNo Then
Exit Sub
            Cells(Target.row + 1, "C").Value = Date
        Else
            Cells(Target.row + 1, "C").Value = Date
        End If
    End If
End Sub
Thanks in advance!!
Andrew

How to convert Columnar Data Range in UPPERCASE using VBA and not formulas?

Dear Forum,

In a Table I need to have certain Columns of Data in UPPER case, so how do I do the same for existing data , I know about the UPPER function but I want to convert the data which is already there with a Macro.

Also, need some suggestions on using the COUNTA function in VBA..I will be copying data from an Company Application in Excel..

I need to make this Data look neater with BORDERS only till the last filled Row and the remaining portion will not have any GRIDLINES..

I am doing this right now, as am sill no good in VBA but applying the Functional approach..

Dim DataRng As String
DataRng = "2:" & WorksheetFunction.CountA(Range("B:B"))
BorderRng ="A1:M" &WorksheetFunction.CountA(Range("B:B"))

Rows(DataRng).RowHeight = 31.5

Range(BorderRng).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone

'=====The code for geting Borders===== This works, however the approach is not VBA oriented so can someone also help me on this as well as getting an Entire Column into UPPERCASE?

This somehow does not work

UCase (Range("B2:B516").Value) Warm Regards
e4excel

I've created a form in excel using VBA for data entry, i'm stuck with a problem :-(

I want the script to find if the value entered in the form is matching the values in column 'A' in the database and if it matches then it needs to select the cell as active cell - to populate the form details. And if there is no matching value found, the script needs to select the last empty cell of the column 'A' to populate the data entered in the form.

1) Form has 10 different fields that needs to be filled by the user.
2) Field 1 - is a text box for 'Request #' to be entered by the user.
3) After filling in all the fields - once clicked on OK, the form should search for the the 'request #' entered on the form in the database (Form and the database are in the same workbook).
4) If the 'Reqeust #' in the Column 'A' matches the the 'Request #' entered in the form, then the matching cell should be selected (Activecell -Were the data can be overwritten, with the new entry)
5)If there is no matching 'Request #' found in the database, the script should loop to select the next available blank cell in column 'A'. So that the form data can be entered.

Hope i didn't confuse :-) , will be great if you could provide some insights and help..

Thanks in Advance!!
Senthil

I have several columns in a spreadsheet, each has a lis of values.
Column B's values depend on the values of Column A -- once a user
selects a value in the list of column A, column B will populate a list
crresponding to the selected value in Column A. There are about 1000
rows in the sheet and for each row, a user has to first select from
Column A list and then Column B list. I have implemented this
funtionality using the method describing here:
http://www.ozgrid.com/News/excel-dependent-lists.htm

Now my boss wants me to do it using macro/VBA. Because the lists in
each cell that I created in the above way are not the control
"listbox", they just appear to be lists. So I don't know how to refer
to a list in a cell and how to program the event when a user selects an
item from the list.

The article mentioned above says this can be done using VBA, but it
doesn't say how. Any suggestions would be highly appreciated!


No luck finding an answer? You could always try Google.