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

Free Microsoft Excel 2013 Quick Reference

VBA Code To Use Same Cell When Row/Columns Inserted

First of all, I wanted the title to be "Dynamic Row And Column Index In Macro" but I wasn't allowed to write it so...

However, I have macros where I write things like:


	VB:
	
.Cells(138, 13).Value = Something 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Now, if I add rows and/or columns in the worksheet, (138, 13) might not be the coorect coordinates anymore.

Is there a way to overcome this problem from the macro?


Post your answer or comment

comments powered by Disqus
Hey,

Thanks in advance for any help. I have a workbook in which I have multiple worksheets which are dependant on one input page. I have vba code to hide and unhide blank rows in one worksheet. What I would like to do is be able to hide corresponding rows in another worksheet. I have run into 2 problems. First, I can write code to check for blank rows, but I want it to update on every enter keystroke and this cycles through 2000 lines and takes too long. I would like to have it execute only when a cell changes. Next, the rows contain the same data, but are arranged in a different order so that row 4 on one worksheet is not the same as row 4 on the other worksheet. Any suggestions?

thanks,

Mike

I was wondering I'm new to VBA coding so I'm kinda looking for help. I have this excel template that I need to generate a text file in...just so I can identify the files, I want to use a specific cell's text as its file name as well as the date when the text file was generated. How can I make this possible?

i have 10 points inserted in colume A, row 2-11(exclude row 1 due to title)
and 10 points(ie.random order 1-10) inserted in colume B, row 2-11
I have a point with value 5.8 in cell D4
and a point with value5.8 in cell D5
i wanna find the point in colume A which is closest to value in D4
and find the point in colume B which is cloest to value in D5

The answer should be the value 6,
but what i want to find is what row is the answer in, and show in the cell D6
so what should appear in D6 is 7, because the value 6 is in the 7th row.

can someone point me a direction of how to do this?
btw, i understand simple VBA code, im using direct cell reference here just to keep it simple.
To whoever cares about my question and spend time on it~
Thanks you! i am really grateful~~

Hi,

I was searching for some code to use VBA to create charts from a data set. In this Forum, I found a sample worksheet that does pretty nearly what I need, however, it needs modification because my data format is slightly different. In the attached, Multiple_Charts_Sample.xls, the x values for the chart are contained in the row range E6: BE6. Also,
multiple data sets are plotted on each chart. What I need is one chart for each set of y values; the x values (dates) are the same for all the charts.

Also, the number of datasets (y value sets) can vary from just a few, to 100. The example worksheet shows 12 data sets of closing stock prices. The example worksheet with the webquery and downloaded data is multi-download_example.xls, also attached.

To summarize, the changes that I need are:

Use the VBA code from Module 1 in Multiple_Charts_Sample.xls to create charts on the Charts worksheet of multi-download_example.xls using:

x-value data contained in column C, over the range C8-C1000
y-value data contained in columns N3 -DG3
one chart per column of per pairing of date data in column C with the price (y) data in columns N- DG
chart type can be a line chart

Any help in modifying the VBA code in Module 1 in Multiple_Charts_Sample.xls to do this would be greatly appreciated.

Thanks,

Art

I am running Excel 2007 on Windows 7.

I have a workbook that I created in Excel 2003. It has extensive VBA code to help run it. When I switched over to Excel 2007 I encountered all sorts of code issues that I had to resolve. I have ironed them all out but ONE. HEre is my issue:

I have some cells that will not allow me to change their format. The worksheets/workbook has no protection (nor has it *ever*). The way I discovered this is my VBA code needs to change the border style on some cells. It throws an error when it cannot complete this step.

I can select the cell and change the contents. But I cannot change the formatting at all.

This issue seems to crop up while I am using the workbook. If I go back to an older version or close without saving, I can sometimes get the code with the formatting to run. But soon after it will crash. It is as if my code were locking some cells but I have *never* used any protection/locking ability in my code.

I tried running Microsoft Office Diagnostics but it ran just fine with no errors.

Here is the first few lines of one spot where the code is erroring. IT will error anytime that the code tries to change the format of the cell:
Private Sub OADPrimeTargetBox_LostFocus()
    Dim CompCol As String
    Application.ScreenUpdating = False
    ASeed_Values
    GV.APrimeBoxSel = OADPrimeTargetBox.ListIndex
    Range(Actos_Col_Let & AStartRow - 1 & ":" & AGeneric_Col_Let & AStartRow - 1).Font.ColorIndex =
1
    Range(Actos_Col_Let & AStartRow - 1 & ":" & AGeneric_Col_Let & AStartRow - 1).Font.FontStyle =
"Regular"
    With Range(Actos_Col_Let & AStartRow - 1 & ":" & AGeneric_Col_Let & Cells.Find("*",
[A1], , , xlByRows, xlPrevious).Row)
        .Borders(xlEdgeLeft).LineStyle = xlLineStyleNone
        .Borders(xlEdgeTop).Weight = xlThin
        .Borders(xlEdgeBottom).Weight = xlThin
        .Borders(xlInsideHorizontal).Weight = xlThin
        .Borders(xlEdgeRight).LineStyle = xlLineStyleNone
        .Borders(xlInsideVertical).LineStyle = xlLineStyleNone
    End With
Here is the error: Run-time error '1004': Unable to set the LineStyle property of the Border class

It happens on that first line with .Borders (line 9).

Hi, this might be a simple thing to do but not for me.

I would like to have a vba code that changes the cell color so that if there is number 1 (just number 1) in some cell the background of the cell changes in to shade of grey and cell that has number 2 changes into darker shade of grey and so on. I have numbers 1-10 in random order in my sheet. I mean there is many cells that has the same number and the sheet is quite large so vba code would be ideal choise to do it quickly.

is this possible.

I am running a macro to check statuses and currently I have created the following code to check if the previous rows have also failed. I am wondering if there is an easier way to represent this. My code is below:

Sub test

Dim xRow As Integer 'Used to indicate row on report spreadsheet
Dim wks2 As Worksheet 'Short for worksheet name, "Pivot" in workbook "Reporting.xls"
Set wks2 = Workbooks(Reporting).Sheets("Pivot")
wks2.Activate
For xRow = 5 To wks2.UsedRange.Rows.Count
If Trim(wks2.Cells(xRow, 7)) = "Failed" And Trim(wks2.Cells((xRow - 1), 7)) = "Failed" Then
wks2.Cells(xRow, 9) = "Second Failure"
End If
If Trim(wks2.Cells(xRow, 7)) = "Failed" And Trim(wks2.Cells((xRow - 1), 9)) = "Second Failure" Then
wks2.Cells(xRow, 9) = "Third Day of Failures"
End If
If Trim(wks2.Cells(xRow, 7)) = "Failed" And Trim(wks2.Cells((xRow - 1), 9)) = "Third Day of Failures" Then
wks2.Cells(xRow, 9) = "Fourth Day of Failures"
End If
If Trim(wks2.Cells(xRow, 7)) = "Failed" And Trim(wks2.Cells((xRow - 1), 9)) = "Fourth Day of Failures" Then
wks2.Cells(xRow, 9) = "Fifth Day of Failures"
End If
If Trim(wks2.Cells(xRow, 7)) = "Failed" And Trim(wks2.Cells((xRow - 1), 9)) = "Fifth Day of Failures" Then
wks2.Cells(xRow, 9) = "Multiple Failures"
End If
Next xRow

End Sub

Hi,

I need some VBA code that invokes the Print dialog box.

I cannot use the Printer icon in Excel to do this because
every time the users need to print the current spreadsheet
they first need to do a backup. They always remember to
get a printout (they need the hard copy) but they forget
to do a backup. This spreadsheet is updated with realtime
data several times each hour so a backup is necessary.

I have already implemented the VBA code in a button
to do the respective backup.

This button has the label "Do Backup".

Now I would like to add a few lines of VBA code to the
same button so that AFTER the backup is done the Print
dialog box shows up.

The button label would then be changed to
"Do Backup and Print ..."

Can you give me a few lines of VBA code that I can
append to do this?

Thanks,
Luther

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

I have 4 sheets, each with a table of values. Each table has a drop down box which causes the values in the tables to change.

On a separate sheet i need the total of the these 4 tables of values but when the drop down lists show a specific value.

I thought of maybe recording a macro or using some VBA code to do this, but when i start recording and then change the value in the drop down box, the macro doesn't seem to have recorded anything.

Any suggestions on the best way to do this?

Hello

I've got an excel sheet where a user can copy and paste a single line of data from an HTML table. This data is then sent by VBA code to access database. Done this way as access wouldn't accept html data in the form its written but excel does separate data in the correct way. I have no way of influencing the webpage and i didn't want to get involved in coding the access with custom clipboard. once the data is sent to the database, the form clears in readiness for the next line of data to be copied.

so i have about 10 fields of data. what i want to do is produce a label with only 3 of the fields printed to it. I need to produce 4 labels that are an exact copy.The label size is 7.2 x 3.6cm

I already have defined strings to store data for each field before a sqlinsert into access

what would be the best way of doing this?

many thanks

Hi Guys,

Please I need a vba code to find Invoice No. in a database and replace content of a cell in the same row.

I have a Sales database with 7 columns, as follows:

1. Invoice No. - Column A
2. Invoice Date - Column B
3. Month of Sale - Column C
4. Gross Amount - Column D
5, Vat Amount - Column E
6. Net Amount - Column F
7. Date Paid - Column G

I have created a Multipage Excel Userform for - (1). Sales Data Entry; and (2).Receipts Entry.

I already have a vba code for the Sales Data Entry, and the code works fine. When an invoice is generated, the Sales Data Entry part of the Multipage Userform is used to enter the sales invoice details onto the database. At this stage, what is entered in the last column i.e. Column G is the word 'UNPAID'.

The second page of the Multipage Userform is to be used to enter Receipts and update the database. That page contains only two textboxes, namely - (1) Invoice No. and (2) Date Paid, and a command button named 'Enter Receipt'.

Please I need a macro to run the page 2 of multipage userform (i.e. the 'Receipts' page) - so that when a user enters the Invoice No. and Date Paid, and clicks the 'Enter Receipts' button, the macro will make a search in Column A of the database for the Invoice No. that has been paid. If the Invoice No. is found, then replace the word 'UNPAID' that is entered against the invoice no. in Column G with the Date Paid (in dd/mm/yyyy format).

I need to clarify that each Invoice No. is unique and is never repeated in Column A (i.e. an invoice number cannot appear more than once in column A). The vba code should also be able to provide a message saying 'Invoice No. not found' if the search does not find an invoice no. that is keyed into the 'Receipts' userform (may be in error).

Thanks in advance for your kind help.

Buddy

Hi,

I am currently working on spreadsheet which is using a mix of Conditional Formatting, Validation and a bit of VBA code.

The spreadsheet is a log of vacancies and applicants, When "vacancy" is selected in A I have introduced some VBA code to lock down cells S:AT on the same row.

The code is working but the issue is it is stopping you using the drop down validation throughout the sheet, if you click the arrow that appears on the cell nothing happens, However you can still access the dropdown by right clicking and choosing the 'Pick from Drop-Down List' Menu Item.

The validation is set up by ranges from another Worksheet.

The Code I am using is:


	VB:
	
 Range) 
    Dim myrow 
    Dim StarLock, EnLock 
    StarLock = 19 
    EnLock = 46 
     
    Application.EnableEvents = False 
    Application.ScreenUpdating = False 
    Application.CutCopyMode = False 
     
     
    If Target.Column = 1 Then 
        myrow = Target.Row 
         
        ActiveSheet.Unprotect 
        If Cells(myrow, 1) = "Vacancy" Then 
             
            Range(Cells(myrow, StarLock), Cells(myrow, EnLock)).Select 
             'With Selection.Interior
             '.ColorIndex = 16
             '.Pattern = xlSolid
             'End With
            Selection.Locked = True 
            Cells(myrow, 1).Select 
            ActiveSheet.Protect 
        End If 
         '    Else
        If Cells(myrow, 1) = "Applicant" Then 
             
            Range(Cells(myrow, StarLock), Cells(myrow, EnLock)).Select 
             'With Selection.Interior
             '.ColorIndex = 34
             '.Pattern = xlSolid
             ' End With
            Selection.Locked = False 
            Cells(myrow, 1).Select 
            ActiveSheet.Protect 
        Else 
             
             '        Range(Cells(myrow, Starlock), Cells(myrow, Enlock)).Select
             'With Selection.Interior
             '.ColorIndex = 2
             '.Pattern = xlSolid
             'End With
             '        Selection.Locked = False
             '        Cells(myrow, 1).Select
             '        ActiveSheet.Protect
        End If 
    End If 
     
    Application.EnableEvents = True 
    Application.ScreenUpdating = True 
    Application.CutCopyMode = True 
     
    ActiveSheet.Protect 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help would be much appreciated.

I am unable to upload the file to this website due to security restrictions but it can be found here:
https://rcpt.yousendit.com/143737095...889d657f64b494

Many Thanks,
Leox5000

Hi,

I am currently working on spreadsheet which is using a mix of Conditional Formatting, Validation and a bit of VBA code.

The spreadsheet is a log of vacancies and applicants, When "vacancy" is selected in A I have introduced some VBA code to lock down cells S:AT on the same row.

The code is working but the issue is it is stopping you using the drop down validation throughout the sheet, if you click the arrow that appears on the cell nothing happens, However you can still access the dropdown by right clicking and choosing the 'Pick from Drop-Down List' Menu Item.

The validation is set up by ranges from another Worksheet.

The Code I am using is:

Private Sub Worksheet_SelectionChange1(ByVal Target As Range)
Dim myrow
Dim StarLock, EnLock
StarLock = 19
EnLock = 46

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.CutCopyMode = False

If Target.Column = 1 Then
myrow = Target.Row

ActiveSheet.Unprotect
If Cells(myrow, 1) = "Vacancy" Then

Range(Cells(myrow, StarLock), Cells(myrow, EnLock)).Select
'With Selection.Interior
'.ColorIndex = 16
'.Pattern = xlSolid
'End With
Selection.Locked = True
Cells(myrow, 1).Select
ActiveSheet.Protect
End If
' Else
If Cells(myrow, 1) = "Applicant" Then

Range(Cells(myrow, StarLock), Cells(myrow, EnLock)).Select
'With Selection.Interior
'.ColorIndex = 34
'.Pattern = xlSolid
' End With
Selection.Locked = False
Cells(myrow, 1).Select
ActiveSheet.Protect
Else

' Range(Cells(myrow, Starlock), Cells(myrow, Enlock)).Select
'With Selection.Interior
'.ColorIndex = 2
'.Pattern = xlSolid
'End With
' Selection.Locked = False
' Cells(myrow, 1).Select
' ActiveSheet.Protect
End If
End If

Application.EnableEvents = True
Application.ScreenUpdating = True
Application.CutCopyMode = True

ActiveSheet.Protect
End Sub

Any help would be much appreciated.
I will attach a copy of the file when I get home as the works IT security is crashing the uploader

Many Thanks,
Leox5000

I am trying to create VBA code to count the number of cells within a range that have either text or numbers entered in them. This needs to work on a row by row basis, i.e. the same code applied to row 6, 7, 8 onwards.

I have 12 cells in a row range that are being written into the spreadsheet using another code.

The activecell is set as a cell in column B

The 12 cells (labelled 'Deployments') are found in columns AL:AW and are being written using the codes

to

How do I create a countif code that is able to reference these values?

Can post a sample worksheet if it helps. Alternatively, the worksheet can be found under a previous thread

http://www.excelforum.com/excel-prog...d-working.html

Thanks

Hello,

I have 2 excel files:
File1.xls - this file has Sheet1 that contains some numbers in the first column only.
File2.xls - here i want to copy the data from File1.xls
What i am trying to do is to use a code that looks like this:

	VB:
	
 copytct() 
    Dim i As Integer, r As Integer, x As Integer, c As Integer, form As String 
     
    Sheets("final").Activate 
    Call lr 
    x = LastRow + 3 
    r = 1 
    For i = x To (x + 108) Step 1 
        form = ""=[Sheet1.xls]Sheet1!R" & r & "C1"" 
        Cells(x, 1).Select 
        ActiveCell.FormulaR1C1 = "form" 
        r = r+1 
    Next i 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Call lr calls a function that calculates the last row of the sheet
What i want is to put into cell (x,1) data from File1/Sheet1/cell (1,1)
Then in cell (x+1,1) data from File1/Sheet1/cell (2,1) and so on.
i cannot find the right code to get the adress for the cells in the first file. Variable "form" is my problem. I cant put "r" into the formula "=[Sheet1.xls]Sheet1!R1C1"

I hope i described this ok.
If any questions please ask. And thank you for help.

Danniy

Hi,

I want to select the cell after the last row used in Column A.
for example, I have some data till row A1000 so need to put the formula in Cell A1001 as Code:
each time the data in Column A differs, If the last used row in Column A  is A2500 i need a code to select the cell after the
last row A2501 and insert the formula 
	Code:
	
Everyday the data differs, so i want a macro to find next cell of the last row in Column A and insert the formula
accordingly.

Here's a nice easy one for anybody whose used excel for more thaan a week unlike me, I'm using the following code to access a popup calendar:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("b11122:b12110")) Is Nothing Then frmCalendar.Show_Cal
End Sub
How is this adapted to get the same calendar effect in a range of cells in say row d.

Thanks for any help

Looking for some help with VBA code to do some search and calculations.

I have 4 Columns (A-B-C-D) for sake of argument. I have raw numbers in
Columns A & B and calculations in C & D. There may be anywhere from 10 to
500 Rows. Possible more. A & B may or may not be “color” filled. A with a
Lime Green and B with a Red fill.

What I need to do is search Column A from top down until I find a Green
fill. After I locate the fill, I then need to move to Column B and search
for a Red fill.

If the cell in Column B adjacent to the Green fill has a Red fill, I need to
then subtract Column B from Column A and return the results in Column C. If
there were no Red fill, the search would continue down B until a Red fill is
found and then subtract the Red cell from the Green cell and return the
results in Column D.

After subtracting the Red from the Green, the next step would be to drop one
Row and return to Column A in search of a Green fill. If nothing is found,
the search should continue down Column A until a Green fill is located. When
located, I then need to subtract the previous Red fill from Column B, Row ‘n’
from the new found Green fill Column A, Row ‘n’ and return the result in
Column D in the same Row as the Red fill.

If there is a Green “and Red fill in the same Row, the Red in Column B gets
subtracted from the Green in Column A; however, the Green would not be
subtracted from the adjacent Red. Again, the search would need to continue
down Column A until the next Green fill is located.

The search/calculations should stop once an empty cell is reached in Column
A or B.

The following is a sample/example from one of my spreadsheets. Figures in
Column C are calculated based on a Green fill in A and those in D from a Red
fill in B. (Copy/Paste did not include the color fill)

A B C D
42.43 41.35
41.72 41.05
41.35 40.82
41.16 40.56 0.81
41.48 40.88
41.64 40.35 (2.40)
42.09 41.18
42.29 41.67
42.75 42.05 0.70 (0.77)
42.53 42.22
42.63 41.67
42.82 42.15 1.30
42.75 42.07
42.46 42.00
42.38 41.85
42.40 41.64
41.98 41.52 (1.01)
42.05 41.59
42.53 41.93 2.09
42.19 41.80
42.24 41.68
42.03 40.90
41.08 40.69
41.01 40.44 (2.29)
42.04 40.88
42.11 41.64
42.45 41.93
42.73 41.32 3.41
41.62 40.60
41.05 40.28
40.99 40.32
40.65 40.26
40.94 39.32

Any help with some code would be greatly appreciated.

Thanks
SHD

--
SHD

Excel 2000 (SP-3) running on Win 2000 Professional (SP 4)

This sounds like a Microsoft internal error, but I'm posting just in
case it's something I'm doing wrong and can correct. I apologize if
this is not new, but I searched diligently and didn't find anything,
but it is kind of hard to search for.

I have a sheet with a row in which each of 98 cells contains a counting
formula for a value in the cells above it. Rows above it are inserted
programmatically. Each row inserted is within the existing row range.
The formulas in every one of the cells to the left and right of one
particular cell are adjusted correctly by Excel after a row is
inserted, but the formula in this one single cell does not change. The
format of the formula in the "corrupt" cell is identical to the others;
the VBA code does not reference this cell at all; and the sheet is
protected, so neither my code nor a user could have corrupted this
manually (This is a commercial application with about a hundred users.
A user sent me this corrupt workbook).

The formula in adjacent cell to the left: =COUNTIF(BL$14:BL$122,$A125
The formula in adjacent cell to the right: =COUNTIF(BN$14:BN$122,$A125)
The corrupt formula: =COUNTIF(BM$14:BM$23,$A125)

The problem is that the corrupt formula should reference rows 14:122 as
well.

An interesting fact is that if I change the corrupt formula to be
correct, i.e., to reference the same row range as the adjacent cells,
after that every time I insert a new row the corrupt cell forumal does
get adjusted by Excel. But if I just change it to an arbirary row
range, just for the sake of making a change, e.g., it is still not
referencing the same row range as the adjacent cells, it does not
adjust when a row is inserted.

The formula in every cell started out referencing the range Row 14:19.
The formula was plugged into the first cell in the row programatically,
then copied to each adjacent cell:

indSched.Range(Cells(indx, 3), Cells(indx,
lastInterval)).Select
Selection.FillRight

Since the first (seed) cell's formula is still correct, the corrupt
cell's formula should also be correct.

The corrupt formula evidently worked for 4 row insertions, then after
that stopped working, while the rest continued to adjust after each
insertion (and still continue to change). Of course, it's also possible
all formulas were adjusted correctly until the 100th row, or any
arbitrary number, was inserted, then for some reason the formula in
this cell got clobbered with a bad row number. After that, as shown
above, the formula would no longer work as additional rows were
inserted.

In the interest of simplicity I omitted one fact in the above
description, tho I'm not sure it's relevant. Instead of a single
"counting" row with this problem, I actually have six adjacent rows,
each counting a different value in the rows above, but otherwise the
same formula. In that particular column (BM), the formula in every cell
stopped changing after row 23 was inserted and behaves the same way.

I'm keenly interested not so much in fixing this particular customer's
workbook, when I can obviously do manually, but in preventing this in
the future. This is the second customer to report this problem and it's
beginning to cast doubt on the dependability of the application.

I hate to think I have to create VBA code to make these formula changes
manually, thus duplicating functionality Excel already provides, but at
this point that begins to look like the only option.

Hi all,

Apologies for making my first post on here a request for help - I have been trying to tweak a VBA code to get the result I am after, however failing miserably.

I've got an Excel file that I am using to calculate some approximations for anisotropic material data for use with FEA. All of the data lies on Sheet1. The data that I want to export starts on row 30, and ends on row 33.

The actual cells that I want exporting are:

A30
A31-H31
A32-H32
A33-E33

I need the output seperated by commas. My current code is below (taken off the net):

Public Sub ExportToTextFile(FName As String, _
    Sep As String, SelectionOnly As Boolean, _
    AppendData As Boolean)

Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim CellValue As String


Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile

If SelectionOnly = True Then
    With Selection
        StartRow = .Cells(1).Row
        StartCol = .Cells(1).Column
        EndRow = .Cells(.Cells.Count).Row
        EndCol = .Cells(.Cells.Count).Column
    End With
Else
    With ActiveSheet.UsedRange
        StartRow = .Cells(1).Row
        StartCol = .Cells(1).Column
        EndRow = .Cells(.Cells.Count).Row
        EndCol = .Cells(.Cells.Count).Column
    End With
End If

If AppendData = True Then
    Open FName For Append Access Write As #FNum
Else
    Open FName For Output Access Write As #FNum
End If

For RowNdx = StartRow To EndRow
    WholeLine = ""
    For ColNdx = StartCol To EndCol
        If Cells(RowNdx, ColNdx).Value = "" Then
            CellValue = Chr(34) & Chr(34)
        Else
           CellValue = Cells(RowNdx, ColNdx).Value
        End If
        WholeLine = WholeLine & CellValue & Sep
    Next ColNdx
    WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
    Print #FNum, WholeLine
Next RowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum
End Sub


Sub DoTheExport()
    Dim FileName As Variant
    Dim Sep As String
    FileName = Application.GetSaveAsFilename(InitialFileName:=vbNullString, FileFilter:="Text Files
(*.txt),*.txt")
    If FileName = False Then
        ''''''''''''''''''''''''''
        ' user cancelled, get out
        ''''''''''''''''''''''''''
        Exit Sub
    End If
    Sep = Application.InputBox("Enter a separator character.", Type:=2)
    If Sep = vbNullString Then
        ''''''''''''''''''''''''''
        ' user cancelled, get out
        ''''''''''''''''''''''''''
        Exit Sub
    End If
    Debug.Print "FileName: " & FileName, "Separator: " & Sep
    ExportToTextFile FName:=CStr(FileName), Sep:=CStr(Sep), _
       SelectionOnly:=False, AppendData:=True
End Sub
Current problems:

1. Exports the whole sheet, including data above/below the section I want to export.
2. Exports blank cells (ie. B30-H31 and G33-H33) and prints ", , ," to .txt file.
3. I dont really want to offer the user the option of choosing a seperator - Ideally I would like it to be fixed as a comma but still offer the option of choosing file name and location.

The text file needs to be exported in the same 'order' as the cells are on the sheet (ie. A30, <new line>A31-H31,<new line>A32-H32,<New line>,A32-F32.)

Any help would be much appreciated!

Kind regards

Tom

Hello I am trying to get the VBA code to delete a row if there is no data in a cell in that row. for example if D5 is blank delete row 5.

I can get the Code to do this function row by row but I dont want to write it out 5000 times. Is there a formula to say repete function between rows 18-5000

See enclosed file.

I have this file where if a user makes a change to the cell entries in the first column then values in same row for cols C and D are cleared out. I dont want user to be able to input values into cells in column C and D. I only want VBA code to be able to make changes to cells in cols Cd and D. Not very familiar with protecting, locking cells.........is there anything i need to be concerned about?

I'm still a rookie at this and can readily use any help y'all can provide. I need to have some VBa code to do the following things:

1. Conditionally format a range - cells in "a" should be filled in red with white bold text when value is >20
(value of a is detrmined by subtracting the date in column b from the System date auto entered in c1)

2. Prevent the user from inserting/deleting rows from the Insert menu and from the right click menu

3. Allow users to insert rows that will be properly formatted by using a button (ax:hX) - they should also be able to delete a row (ax:hx).

I realize that this is a rather large request but I am hopeful that there is indeed a generous individual in the group willing to lend me his/her expertise.

Thank you

DummyBook2.xls

Hi all,

I am reasonably new to VBA so any help would be appreciated. I'm using excel 2007 but I'd like the workbook to be compatible with older versions if possible.

I have a worksheet with a table for filling out employee data. I want users to be able to enter how many employees they have into a text box and for the table to then display that number of rows for them to fill out. The number of employees ("X") can vary from 1 to 100.

I was thinking the best way to do it would be to start with all the rows of the table hidden (i.e. rows 1:100) and then have some code that unhides rows 1:X whenever the number in the text box is changed. Trouble is I'm not sure how to do this.

Thanks in advance
G


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