Free Microsoft Excel 2013 Quick Reference

Number of rows in excel worksheet Results

I have a macro in excel that is copying out to another newly created workbook then saving that workbook as a tab delimited (.txt) file.

The issue that I am having is that I am trying to upload this file into another application (MS Dynamics/Great Plains) and it has a large number of characters trailing at the end of the data.

For Example say I have the following in excel in columns and a row

Column1 Column2 Column3
Fred Smith RI
George Jones CA
Carl White VA

When I copy this out I expect that the last character would be the letter A in VA in Column 3. However what I end up with is 40 or so characters after it. Now in the excel worksheet I do have formulas that carry down below the total text in order to ensure that the formula is there for any possible number of rows. Is this the issue?

So I guess my question is: A how do I eliminate these "blank" characters in excel OR how do I do so if the file is in notepad (can I apply an Excel Macro to notepad)

Or, if its my extra formulas; is there a way to copy a formula down only so far as the last row of data. So for example in the data above I have the data displayed in Column 3 is actually from a vlookup that carries down 50 rows (it is told to be "" if column 1 is empty) in case I have 50 rows of data. I could have the formula only in one cell and copy it down just only so far as needed.

Hopefully this makes sense.

Here is the relevant part of the code I am using:


	VB:
	
 EntrySetup() 
     
     
     
     
    Application.ScreenUpdating = False 
     
    Calculate 
     
     
    Sheets("Entry Prep").Select 
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh 
     
    Calculate 
     
    Sheets("Entry Prep").Select 
    Range("E5:E1000").Select 
    Selection.Copy 
    Sheets("LoanSalesEntry").Select 
    Range("G2").Select 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
     
    Sheets("Entry Prep").Select 
    Application.CutCopyMode = False 
    Range("F5:F1000").Select 
    Selection.Copy 
    Sheets("LoanSalesEntry").Select 
    Range("D2").Select 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
     
    Sheets("Entry Prep").Select 
    Application.CutCopyMode = False 
    Range("H5:I5000").Select 
    Selection.Copy 
    Sheets("LoanSalesEntry").Select 
    Range("E2").Select 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
     
    Sheets("Entry Prep").Select 
    Application.CutCopyMode = False 
    Range("J5:J5000").Select 
    Selection.Copy 
    Sheets("LoanSalesEntry").Select 
    Range("B2").Select 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
     
    Range("A1").Select 
     
    Calculate 
     
     
     
    Sheets("LoanSalesEntry").Select 
    Sheets("LoanSalesEntry").Copy 
    ActiveWorkbook.SaveAs Filename:= _ 
    "T:AccountingDynamicsUploadEntryLoanSalesEntry.txt", FileFormat:=xlText _ 
    , CreateBackup:=False 
     
    ActiveWorkbook.Close False 
     
     
     
     
End Sub[COLOR=#3E3E3E][B][/B][/COLOR] 

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


I need to return the "Subject" of any type of file (.txt, .pdf, .docx, etc.). The subject property does not need to be changed, only read. I got it to work for Excel files with tags, but it doesn't return subjects of PDFs. Maybe an "IF" "THEN" statement or "CASE" for different doc types? I have no clue.

FIRST TRIAL
Sub ReturnSubjectOfFiles()
Dim objShell As Object, objFolder As Object
Dim iCounter As Long, iRow As Long, iCol As Integer
Dim strFileName As Variant
Dim sDir
sDir = Cells(1, 1).Value

Application.ScreenUpdating = False
Range("A:C").ClearContents
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(sDir)
iRow = 3
iCol = 1
For Each strFileName In objFolder.Items
iCounter = 22
Cells(iRow, 3).Value = objFolder.GetDetailsOf(strFileName, iCounter)
Cells(iRow, 2).Value = objFolder.GetDetailsOf(strFileName, 0)
iRow = iRow + 1
Next strFileName
Application.ScreenUpdating = True
End Sub

SECOND TRIAL: More extensive, includes subfolders as well (LOVE THIS)- $5 kicker if you can incorporate this as well. (This is within a form I created)

Private Sub cmdRunReport_Click()
'
' Requires Private Sub below!
'Unprotect Sheet while macros run
Dim sh As Worksheet
Dim myPassword As String
myPassword = "2012"
For Each sh In ActiveWorkbook.Worksheets
sh.Unprotect Password:=myPassword
Next sh

If Cells(2, 2) "" Then
Columns("B:G").Select
Selection.Delete Shift:=xlToLeft
End If
Worksheets("Sheet1").Range("A2") = TextBox2
Worksheets("Sheet1").Range("A3") = TextBox3
'Sets folder as variable; user inputs folder name into text box txtFldrName (Risk/Mitigation)
Dim FolderName As String
FolderName = txtFldrName
Dim FileName As Variant
Dim FileCollection As New Collection ' create a collection of filenames
'Filling a collection of filenames (search for files in FolderName and include files located within subdirectories)
Call FileSearch(FileCollection, FolderName, "*.*", True)
'Print file name list to column B, print date of last review to column E
For Each FileName In FileCollection ' cycle for list(collection) processing
Dim iRow As Integer
Dim iCol As Integer
iCol = 2 'Place file names in column B
iColDate = 5 'Place keyword/date of last review into column E
iRow = 2 'Place first file name into the second row

Do While Cells(iRow, iCol).Value ""
iRow = iRow + 1
Loop

Cells(iRow, iCol).Value = FileName
Cells(iRow, iColDate).Value = FileDateTime(FileName) '(Risk/Mitigation)-change to keyword
Next FileName
'Return number of rows of data
Dim NumEntries As Integer, myRange As Range
Set myRange = Columns("B:B")
NumEntries = Application.WorksheetFunction.CountA(myRange)
' Print to immediate debug window and message if no file was found
'(Risk/Mitigation)-Add instructions on how to find correct file
If FileCollection.Count = 0 Then
Debug.Print "No file was found !"
MsgBox "No file was found !"
End If
'Convert full file path name to file name (i.e. cut off folder name string)
Range("D1").Select
ActiveCell.FormulaR1C1 = "=IF(R1C1"""",LEN(RC[-3]))"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]"""",LEN(RC[-2]))"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]"""",RIGHT(RC[-1],RC[1]-(R1C4+1)),"""")"

'Fill equations down to row 10,000
'(Risk/Mitigation)
'Cut all new data; re-paste as values without equations (saves space)
Range("C2:D2").Select
Selection.AutoFill Destination:=Range("C2:D10000"), Type:=xlFillDefault

'Remove equation dependencies
Columns("C:C").Select
Selection.Copy
Columns("C:C").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'Get number of days since last review
Columns("D:D").Select
Selection.ClearContents 'previously, data was used to truncate file name
Range("D2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]"""",ROUNDUP(R14C1-RC[1],0),"""")"

'Assign status to number of days since last review
'(Risk/mitigation) Will this change? Management requirements- consider adding password protection to cells A2 and A3
Range("F2").Select
'While cell value in Column B is not empty, enter "Red" if D2>$A$15, enter "Yellow" if D2>$A$16, and "Green" for all others
ActiveCell.FormulaR1C1 = _
"=IF(RC[-4]"""",IF(RC[-2]>R15C1,""Red"",IF(RC[-2]>R16C1,""Yellow"",""Green"")),"""")"

' Add Headers to Rows
Range("B1").Select
ActiveCell.FormulaR1C1 = "Full File Name"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Abbreviated File Name"
Range("D1").Select
ActiveCell.FormulaR1C1 = "# Days Since Last Review"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Last Edit"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Status"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D10000"), Type:=xlFillDefault
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F10000"), Type:=xlFillDefault
'(Risk/Mitigation) too many files in folder to create list
'Remainder of macro is for FORMATTING ONLY
Range("D1").Select
With ActiveCell.Characters(Start:=1, Length:=24).Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Range("E1").Select
With ActiveCell.Characters(Start:=1, Length:=9).Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Range("F1").Select
ActiveCell.FormulaR1C1 = "Status"
Columns("B:F").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlHairline
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlHairline
End With
Range("B1:F1").Select
Selection.Style = "40% - Accent1"
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

Columns("F:F").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""Green"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Columns("F:F").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""Yellow"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16751204
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 10284031
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Columns("F:F").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""Red"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("B1:F1").Select
Columns("C:C").ColumnWidth = 53.14
Columns("E:E").EntireColumn.AutoFit
Columns("E:E").Select
Selection.NumberFormat = "m/d/yyyy"
Columns("E:E").EntireColumn.AutoFit
Range("B1:F1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("C1").Select
Columns("B:B").ColumnWidth = 10.29
Columns("D:D").ColumnWidth = 11.14
Rows("1:1").EntireRow.AutoFit

Columns("C:F").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With

Worksheets("Sheet1").Range("A1") = txtFldrName
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]"""",RIGHT(RC[-1],(LEN(RC[-1])-LEN(R1C1))-1),"""")"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C10000"), Type:=xlFillDefault
For Each sh In ActiveWorkbook.Worksheets
sh.Protect Password:=myPassword
Next sh
End Sub

Private Sub FileSearch(pFoundFiles As Collection, pPath As String, pMask As String, pIncludeSubdirectories As Boolean)
'
' Search files in Path and create FoundFiles list(collection) of file names(path included) accordant with Mask (search in subdirectories if enabled)

Dim DirFile As String
Dim CollectionItem As Variant
Dim SubDirCollection As New Collection
'(Risk/Mitigation)Add backslash at the end of path if not present
pPath = Trim(pPath)
If Right(pPath, 1) "" Then pPath = pPath & ""
' Searching files accordant with mask
DirFile = Dir(pPath & pMask)
Do While DirFile ""
pFoundFiles.Add pPath & DirFile 'add file name to list(collection)
DirFile = Dir ' next file
Loop
' Procedure exiting if searching in subdirectories isn't enabled
If Not pIncludeSubdirectories Then Exit Sub
' Searching for subdirectories in path
DirFile = Dir(pPath & "*", vbDirectory)
Do While DirFile ""
' Add subdirectory to local list(collection) of subdirectories in path
If DirFile "." And DirFile ".." Then If ((GetAttr(pPath & DirFile) And vbDirectory) = 16) Then SubDirCollection.Add pPath & DirFile
DirFile = Dir 'next file
Loop
' Subdirectories list(collection) processing
For Each CollectionItem In SubDirCollection
Call FileSearch(pFoundFiles, CStr(CollectionItem), pMask, pIncludeSubdirectories) ' Recursive procedure cal
Next

frmNameStatusRept.Hide
End Sub

Private Sub CommandButton1_Click()
frmFolderPathHelp.Show
End Sub

Private Sub CommandButton2_Click()
Sub ClearFields()
'
' ClearFields Macro
'
Columns("B:G").Select
Selection.Delete Shift:=xlToLeft
'
End Sub
Private Sub Frame2_Click()
End Sub

My spreadsheet needs VBA code edit, to send EMAILS to rows, that are NOT BLANK in the worksheet RESULTS, excluding the HEADER.

See sample file http://www.srands.co.uk/exoftable3.xls

Issue1: To get VBA code working edited (See code below, 9th line, For r = 2 To 2 'Needs editing so that data in 'not blank' rows only is included. Also r = row, not to be confused with column r) to return email for only the 2nd row (Row after HEADER) entry only, on the RESULTS page. However the number of not blank rows is a variable, and will depend upon the rows that meet the criteria in 'WORKSHEET' in column U.

Issue2: If I expand the row range to a full page upto row 51, many BLANK emails are generated (Because of blank rows, the auto-generated fields would be BLANK).
I don't know what VBA code to use instead though.
In formula's for the 'RESULTS' page I would use a command that checks if the row is not blank, something like =IF(AND(A2=0),"",'email command')WHAT IS THE VB EQUIVALENT OF SOMETHING LIKE THIS?
For this spreadsheet the number of RESULTS will be unknown depending on the information/data available, hence I want to include NOT BLANK entries from rows 2 to 51.

PURPOSE OF DESIRED SOLUTION:
CODE NEEDS EDITING, JUST TO COUNT NOT BLANK ROWS IN THE WORKSHEET 'RESULTS':


	VB:
	
 ShellExecute Lib "shell32.dll" _ 
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _ 
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _ 
ByVal nShowCmd As Long) As Long 
Sub SendEMail() 
    Dim Email As String, Subj As String 
    Dim Msg As String, URL As String 
    Dim r As Integer, x As Double 
    For r = 2 To 2 'Needs editing so that data in 'not blank' rows only is included
         '       Get the email address
        Email = Cells(r, 10) 
         
         '       Message subject
        Subj = "Your car for sale.  " & Cells(r, 1).Text & "." 
         '       Compose the message
        Msg = "" 
        Msg = Msg & "Dear " & Cells(r, 11) & "," & vbCrLf & vbCrLf 
        Msg = Msg & "I like your car, the " & Cells(r, 1).Text & "." & vbCrLf & vbCrLf 
        Msg = Msg & "Please call me back.  " 
        Msg = Msg & "It is " & Cells(r, 2).Text & "." & vbCrLf & vbCrLf 
         
        Msg = Msg & "Cheers " & vbCrLf & vbCrLf 
        Msg = Msg & "Stephan Rands" & vbCrLf 
        Msg = Msg & "07772000679" & vbCrLf 
        Msg = Msg & "[EMAIL="mail@srands.co.uk"]mail@srands.co.uk[/EMAIL]" 
         
         
         '       Replace spaces with %20 (hex)
        Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20") 
        Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20") 
         
         '       Replace carriage returns with %0D%0A (hex)
        Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A") '       Create the URL
        URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg 
         '       Execute the URL (start the email client)
        ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus 
         '       Wait two seconds before sending keystrokes
        Application.Wait (Now + TimeValue("0:00:02")) 
        Application.SendKeys "%s" 
    Next r 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
See sample file http://www.srands.co.uk/exoftable3.xls

TheWORKSHEET 'RESULTS', has the VISUAL BASIC code called 'Send EMail'. Obviously to view 'RESULTS' worksheet VISUAL BASIC code, View, Tools Bars, Visual Basic, then on the Toolbar press the play symbol (R/H arrow), Step into.

Or to play the MACRO of the rows that meet all criteria in 'WORKSHEET', shown in 'RESULTS', View, Tools Bars, Visual Basic, then on the Toolbar press the play symbol (R/H arrow), Run.

Cheers

Stephan

Cross threads:
http://www.excelforum.com/excel-prog...html?p=2762981
http://www.mrexcel.com/forum/showthr...18#post3122118

Hello everyone,

I am new to excel vba so was wondering if you guys can help on this. My problem scenario is as follows:

I have a customer database that I am managing with excel. It contains the usual information like name, account number, telephone, our branch that the customer belongs in, email address and stuff like these. Everyweek I have to generate a list of a certain group of customers for our analysis. What I do right now is that I select the entire row of the customers who shuld be included in this week's analysis and colour it yellow (index = 35). I have a command button macro doing this.

What I want is to have another command button which will search for the yellow colour cell in Worksheet1 (the first coloumn or in any column; doesnt matter because I have the entire row highlighted yellow anyway). When if finds a yellow colour cell in Worksheet1 it will copy the contents of the ENTIRE ROW and paste that row in another worksheet. The next yellow row should be copied right underneath the previous. After it has done copying and pasting the yellow row, it will remove ONLY the yellow highlight (NO VALUES from cell) from those rows in Worksheet1 so as to reset it for my use next week.

Can this be done? Please help me since my customer number is increasing every month and its putting a lot of pressure on me just to generate that list. Any help is appreciated. Thanks a lot in advance

RH.

Please help as i am new to this whole macro thing.

I have been able to build the attached with some difficulty but the userform only writes to the 1st row of the database and then on the second entry overwrites the 1st entry.

If anyone can help me with this I will be so appreciative, I really am struggling now

The code is as follow's


	VB:
	
 
Private Sub cmdAdd_Click() 
    Dim iRow As Long 
    Dim ws As Worksheet 
    Set ws = Worksheets("DATABASE") 
     'find first empty row in database
    iRow = ws.Cells(Rows.Count, 1) _ 
    .End(xlUp).Offset(1, 0).Row 
     'check for a part number
    If Trim(Me.txtNAME.Value) = "" Then 
        Me.txtNAME.SetFocus 
        MsgBox "Please enter contact name" 
        Exit Sub 
    End If 
     'copy the data to the database
    ws.Cells(iRow, 2).Value = Me.txtNAME.Value 
    ws.Cells(iRow, 3).Value = Me.txtRANK.Value 
    ws.Cells(iRow, 4).Value = Me.txtTHEATRELOC.Value 
    ws.Cells(iRow, 5).Value = Me.txtDATEISSUED.Value 
    ws.Cells(iRow, 6).Value = Me.ListBox1.Value 
    ws.Cells(iRow, 7).Value = Me.txtNEWIRD.Value 
    ws.Cells(iRow, 8).Value = Me.txtNEWICCID.Value 
    ws.Cells(iRow, 9).Value = Me.txtPHONEIMEI.Value 
    ws.Cells(iRow, 10).Value = Me.txtPW.Value 
    ws.Cells(iRow, 11).Value = Me.txtPELI.Value 
    ws.Cells(iRow, 12).Value = Me.txtOLDIRD.Value 
    ws.Cells(iRow, 13).Value = Me.txtOLDICCID.Value 
    ws.Cells(iRow, 14).Value = Me.txtCOMMENTS.Value 
     
     'clear the data
    Me.txtNAME.Value = "" 
    Me.txtRANK.Value = "" 
    Me.txtTHEATRELOC.Value = "" 
    Me.txtDATEISSUED.Value = "" 
    Me.ListBox1.Value = "" 
    Me.txtNEWIRD.Value = "" 
    Me.txtNEWICCID.Value = "" 
    Me.txtPHONEIMEI.Value = "" 
    Me.txtPW.Value = "" 
    Me.txtPELI.Value = "" 
    Me.txtOLDIRD.Value = "" 
    Me.txtOLDICCID.Value = "" 
    Me.txtCOMMENTS.Value = "" 
    Me.txtCOMMENTS.SetFocus 
End Sub 
Private Sub Label1_Click() 
End Sub 
Private Sub Label11_Click() 
End Sub 
Private Sub Label13_Click() 
End Sub 
Private Sub Label3_Click() 
End Sub 
Private Sub Label4_Click() 
End Sub 
Private Sub txtREQUESTDATE_Change() 
End Sub 
Private Sub Label5_Click() 
End Sub 
Private Sub Label8_Click() 
End Sub 
Private Sub Label9_Click() 
End Sub 
Private Sub ListBox1_Click() 
End Sub 
Private Sub txtNAME_Change() 
End Sub 
Private Sub UserForm_Click() 
End Sub 
Private Sub cmdCLOSE_Click() 
    Unload Me 
     
End Sub 

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

Many Thanks

Jay

#1 Let the users only have access to one field for their selection of records.
We assume here that we have four columns and that the sheet only contains data in the table.


	VB:
	
 
 
Sub Hide_Fields() 
    Dim wbBook As Workbook 
    Dim wsSheet As Worksheet 
    Dim rnData As Range 
    Dim i As Long 
     
    Set wbBook = ThisWorkbook 
    Set wsSheet = wbBook.Worksheets(1) 
     
    With wsSheet 
        Set rnData = .UsedRange 
    End With 
     
    For i = 2 To 4 
        rnData.AutoFilter Field:=i, Visibledropdown:=False 
    Next i 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
#2 Autofilter & Protected worksheet
If we only want the end-users to filter and view data then we can apply a technique that allow users to do it although the worksheet is protected.

Depending on the situation it may be necessary to associate the code to other events then what the example use below:


	VB:
	
 
 
Private Sub Worksheet_Activate() 
    With Me 
        If .AutoFilterMode = False Then .UsedRange.AutoFilter 
        .Protect UserInterfaceOnly:=True 
        .EnableAutoFilter = True 
    End With 
End Sub 

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

#3 Looping through the collection of fields & conditions
Sometimes it may be useful to filter the data based on several fields and with one condition per field.

The following example shows how we can do it quite easily. For the solution to be more flexible, the conditions can be retrieved by letting the users add them via an Inputbox.


	VB:
	
 
 
Sub Filter_Multiply_Fields_Conditions() 
    Dim wbBook As Workbook 
    Dim wsSheet As Worksheet 
    Dim rnData As Range 
    Dim vaFields As Variant, vaConditions As Variant 
    Dim i As Long 
     
     'Here we populate the arrays.
    vaFields = VBA.Array(1, 2, 3, 4) 
    vaConditions = VBA.Array("AA", ">=3", "", "

I've got two Excel worksheets for our customers, both of which contain information I need but neither of which has all of the information. There are also some customers on one spreadsheet that are not in the other.

What I'd like to do is to compare a common value between the two Worksheet 1 & Worksheet 2 (i.e. Customer_ID#). If the first customer ID in WS1 matches the first Customer ID in WS2, certain information (i.e.: Sales) is copied from one spreadsheet to another. If it is not a match, the next Customer ID in WS2 is checked, and so on. If there are no matches to a particular Customer ID, the SALES column is left blank (or placeholder text is entered).

Because of the different number of rows in the 2 sheets, a simple sort won't work. I've tried an "If...Then" function, but I can't figure out how to perform the function on all of the records (instead of just one) and how to make it increment while comparing.

I've attached a sample workbook that has samples. (The actual sheets have several hundred entries.)

Am I asking for two much? It sounds simple to do, but I'm not well versed in Excel programming.

Any assistance would be appreciated.

I have the following workbook and probably should have this done in Access, but Excel is problem enough for me right now:

http://www.ashleylandscaping.com/sample.xls

On the "Cust Info" sheet, and when I select a name in the drop down list, it fills in the Cust ID number above. When I have a number in the Cust ID box and click the "Get Customer History" button, It should then look for that Cust ID # in the three different worksheets named "Job History, Invoice History, and Invoice History". It should then copy The Column Heading for each of those sheets and any row with a matching Cust ID.

Ive got other worksheets with macros copying and pasting rows and such, but I couldnt figure out how to copy column heading and the rows. Also I couldnt figure out how to make it paste info without overwriting previous rows because of the variance in the number of rows that may be imported from each of the 3 sheets.

Heres what it looks like now:
http://www.ashleylandscaping.com/now.jpg

And the result should look something like this:
http://www.ashleylandscaping.com/want.jpg

Hi - just joined the forums. I have quite a bit of experience using regular Excel formulas, but not a whole lot of exposure to VisualBasic, and the functionality I'm trying to achieve with my current project seems like it will require a bit of VBA.

What I'm working on is a worksheet that can keep track of progress on work for a particular client or set of clients. Each client has their own set of specifications and parameters for their orders. I've created "template" workbooks for each client which contain rows of blank ActiveX checkboxes and Combo-boxes that are set up for each client's specific specs.

I want to have any particular client's specs loaded into a "master" workbook per each order that the client makes. Right now, I have a Client List combo-box in B1 of the master worksheet, which refers to a list of clients on sheet 2 cells A1-A17. Cell B3 will be a numeric cell where you enter the number of titles that client has ordered. When you enter a number in Cell B3, I then want that number of rows from the client's individual workbook to load into the master worksheet, including all ActiveX check boxes and combo boxes for that client's specs, so that we can then keep track of progress on that client's order.

Here's a simplified example. Say we get an order for 12 titles from client "George." George requires that each title be cropped, resized, encrypted, and shipped on DVD-R. I already have a template workbook with check boxes for each step of the process as well as blank cells that will later be filled with each title George has ordered, titled George_Template.xls .

So, when I go into the master worksheet, I choose George from the client drop-down list, then enter the number "12" in Cell B3 to specify the number of titles that George has ordered. Then, when I hit enter in cell B3, 12 rows from George_Template.xls load into the master worksheet to enable us to keep tabs on this current order.

My thinking is that on Sheet 2 of the master workbook, which contains the list of clients, I would put the regular Excel formulas that reference the individual client workbooks in the B column next to the name of each client, but I have no idea how to get the combo-box to refer both to the client name AND the formula that refers to the client's workbook, let alone how to get Sheet 1 to load only a certain number of rows from that individual client workbook.

I'm not really looking for someone to write out all the code for me, because that's a little unfair, but anything that can point me in the right direction would be greatly appreciated.

Thanks!

I am preparing to design a new spreadsheet, but I don't know how to achieve
one aspect of it.

I will have a variable number of rows, representing my data records, on the
"worksheet" portion. To each row, an attribute will be added by a user.

If the attribute is X, the entire row should be placed in another location
on a report. if it is Y, it is a different location on that report. Once
those row copy/inserts are completed, there may need to be some more
actions...summing, tidying up the report format based on the numebr of rows,
etc.

Any areas of Excel that can help achieve this, pls let me know.

Thanks in advance,
Tom

My problem is that the starting workset will always have a variable number
of rows...and therefor, a variable number of rows of type X or Y to be
inserted into another location.

I don't know how to automate this process...that is...let the user simply
fill in X or Y for each record and then format all the results properly,
because each instance it is done, there is a different number of rows to act
on. So I cannot figure out how to get a macro to act on this data
consistently, or any other method to do what I need done.

I am trying to find something like "Insert Next" so that once X or Y is
applied, that worksheet row is inserted in the next available row on the
finished report. The ultimate would be that any summing functions, and other
report features, at the "bottom" of that data would also move along with it,
and remain at the bottom, no matter how many rows were inserted.

Newbie to VBA

I need to use Conditional Formatting to format a range of cells to have
Wheat background and Red text (Bold) if a text string is contained in
each cell. For example, all cells in a range that contain the text
string "LLC" or "Inc" or "Corp" or "Corporation" or
"Company" , etc.

If cell E2 contains Acme, LLC
I need to give that cell (E2) the condition formatting
If cell E3 contains John Brown
That cell does not get the condition formatting
If cell E4 contains Joe's Pool Hall, Inc
Cell E4 gets the condition formatting

Thanks to Dave Peterson I know how to dynamically find the last row or
all rows used in a spreadsheet as follows:

Sub MaxRow()
'Count the number of rows used in a worksheet
oRowMax = wks.UsedRange.Rows.Count
oRowMax1 = wks.UsedRange.Rows.Count + 1
End Sub

Thanks Dave...

I know how to manually use conditional formatting within a spreadsheet
but when using formulas in CF all I can find is when a cell is "equal
to" a value. Can someone tell me how to use "contains" in VBA CF
code or if it can be done? I would also like to incorporate the code
(MaxRow) from above if possible.

Thanks in advance for any help.

Willie T

Excel 2002 SP3

I'm trying to write a set of macros to read ; text files and do some charting.

So I have Workbooks.OpenText working to open the data properly, but then I need to get a reference to the current sheet of which there is only one. But no reference that I try will work. They all toss an error '91 Object or With block not set.

After I do the OpenText and make the new workbook, then I need to call a series of subs to do format the data and do some charting. The formatting - recorded from a macro, works just fine, so I know the OpenText call leaves the new data as the active sheet, but I need an object reference to the active sheet to continue with the processing, and I can't get that reference.

The code for the first failing routine looks like this:
Function GetDataRange()
' GetDataRange
' Return the number of rows in the data by looking for the first zero value of the time series

    Dim idx As Integer
    Dim jdx As Integer
    Dim interp As Double
    Dim cc As String
    Dim Wks As Worksheet ' holds the current worksheet incase we go away from it.
    Dim Win As Window ' Holds the currently active window

    ' Wks = Win.ActiveSheet ' Application.ActiveSheet
    Wks = Worksheets(1) ' Application.ActiveSheet

    For idx = 10 To 1000
        cc = Wks.Cells(idx, 1).Value
        If cc = "" Then
            Exit For
        End If
    Next idx
GetDataRange = idx - 1 ' return the row of the last data
End Function
I figure I'm doing something simple and stupid, but in spite of 5 inches of books to look through I can't find a working example of what I need.

Thanks much,
ww

Hi,

I'm using ADO with the Excel ODBC driver to query Excel ‘tables’, identified
in the FROM clause by the worksheet name, i.e. SELECT * FROM [MySheet$].

My ‘table’ (i.e. worksheet) contains 160 rows of data. My problem is that
when querying, the driver believes that 11 empty rows below the data are also
part of the ‘table’.

According to Microsoft, the ‘table’, when querying Excel in this way, is
defined as the ‘UsedRange’ of the worksheet specified - see
http://support.microsoft.com/kb/278973/EN-US/. However, when I check the
number of rows in ActiveWorksheet.UsedRange it correctly consists of the
first 160 rows only.

Although I *could* workaround by building a string referring to the range of
the UsedRange object, and including that in the FROM clause rather than just
the worksheet name, I'd like to work out what's going on!

I'm wondering if it's a bug in the Excel ODBC Driver...

I need to place more than the default number of rows in an Excel worksheet,
but don't know how this can be done.

I am developing a VBA function to be used as an Excel worksheet function.
Some error checking is included in the VBA. My problem is that Excel
executes the function before all input arguments have been specified. This
gives rise to some very disturbing messages from my error checking.

How can I prevent Excel from running the function during the specification
of input ?

I have attached a screen dump with a small example that illustrates the
problem. The VBA is below. The screen dump shows the situation when I have
entered ARange and is going on to enter BRange. I click C3 and (while
holding down the Shift key) I try to click D4. But inbetween Excel runs the
function and recognizes that BRange is too small, i.e. BRange is C3.

Any ideas ? I could suggest one myself : Remove all calls to MsgBox, and
assign an error value #VALUE! to the function. Any better ideas ?

Please also answer to Helge.V.Larsen(RemoveThis)@Risoe.DK(AndThis).

Kind regards,
Helge

'=================== VBA start ===================
Option Explicit
Option Base 1

Function AAAA(ByVal aRange As Range, _
ByVal bRange As Range) As Variant

Dim Nrow_a As Integer, Nrow_b As Integer, Ncol_a As Integer, Ncol_b As
Integer
Dim R As Integer, C As Integer

Nrow_a = aRange.Rows.Count
Nrow_b = bRange.Rows.Count
Ncol_a = aRange.Columns.Count
Ncol_b = bRange.Columns.Count

If Not (Nrow_a = Nrow_b And Ncol_a = Ncol_b) Then
Call MsgBox("The two input ranges should have the same number of rows
and columns.", _
vbCritical, "ERROR")
End If

AAAA = 0
For R = 1 To Nrow_a
For C = 1 To Ncol_a
AAAA = AAAA + aRange.Cells(R, C) * bRange.Cells(R, C)
Next C
Next R

End Function
'=================== VBA end ===================

I need to be able to enter a given number into a column in Excel and have
that number of rows added in the next row (copied from another row).

Hi guys, I have a worksheet attached with a range of values in Column B onwards. As you can see the numbers are not arranged properly and I would like to get it sorted by ascending order for each row.. Would be great if someone could help me out.. Thanks..

Good afternoon,

I am trying to fix a macro that creates a pivot table, the problem is the number of rows change each time with a different worksheet. I have been searching but have not been able to fix the issue. Below is the code, I believe the problem occurs at Sheet1!. I appreciate any help thanks.

Sub Macro1()

    Rows("1:2").Select

    Selection.Delete Shift:=xlUp

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _

        "Sheet1!R1C1:R26C45").CreatePivotTable TableDestination:="", TableName:= _

        "PivotTable1", DefaultVersion:=xlPivotTableVersion10

    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)

    ActiveSheet.Cells(3, 1).Select

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("FUND_CODE ")

        .Orientation = xlRowField


Hello everyone,

I am writing a VBA code in excel and I need to have the code search through the entire worksheet and use all columns until the last data entry. Right now the code I have written is:
 'Determine number of items in table
    i = 0
    For t = 4 To 100 Step 1
    L = Format(t, "0")
    If IsEmpty(Worksheets("Sheet1").Range("$A$" & L)) = False Then
    i = i + 1
    End If
Right now this works because I have 100 rows, but I want to make it to where if I added x amount of rows with information VBA would automatically go through the 100 + x rows. Basically, turn the present value of 100 into a variable. If anyone has any suggestions that would be great.

Thank you!!!!

Dear all, in one of my applications I would like to physically limit the number of rows and columns. So, the user cannot navigate beyond - say - 20 rows and 10 columns. Any way to do this in VBA? Thanks!