Posts by cytop

Re: Unable to run the Private Sub Worksheet_Change(ByVal Target As Range) Macro

Think about the logic - just add another IF statement to handle that. It's simple, really.

Re: Unable to run the Private Sub Worksheet_Change(ByVal Target As Range) Macro

Private Sub Worksheet_Change(ByVal Target As Range)


   Select Case True
      Case Not Intersect(Target, Range("C5")) Is Nothing
         '//  Code for C5


      Case Not Intersect(Target, Range("C9")) Is Nothing
         '// Code...
         
   End Select


End Sub

Quote

can this code be run from Module

Not as Event code - you can add it to a module but you need to add code to the Event handler to call the procedure.

Re: Pipe "|" Delimiter CSV?

Opening a file 'For Output' will create the file if it doesn't exist, or truncate any existing file.

Quote

Wouldn't that work...

Yes. You can do that if you wish, but there is no particular advantage doing it that way.

Quote

The "~" is already in the last column

Just covering all bases. You suddenly mention something that is non-standard for a CSV file so tried to cover that. If the character is already in the cells then it will be included.

Re: Pipe "|" Delimiter CSV?

Quote

can I edit this file name code to generate dynamically something like...

Yes, I just used a simple fixed file name as an example. Just be aware that any existing file with the same name will be truncated (effectively overwritten, without warning) as soon as you open the file.

Quote

Will this work as is with a csv file

No problem, you just name the file with any extension you want (Even .XLSM). when writing to a file, the extension you use doesn't really matter as long as the program reading the file can understand the structure.

Quote

this loop is simply going through all used cells in the sheet and printing the contents + "|" into the text file

Yep - nothing complicated there.

Quote

remember to enter ~ as Line End Indicator of each line

For i = 1 To UsedRows 
    For j = 1 To UsedColumns - 1 
        Print #1, .Cells(i, j); "|"; 
    Next j 
    Print #1, .Cells(i, UsedColumns) & "~"
Next i

One thing not clear is if a New Line character is added after that. If it is then no problem but if the other program interprets '~' as the end of the line then it will need a ";" as well

Print #1, .Cells(i, UsedColumns) & "~";

Note the semicolon is outside the double quotes.

You can"Fill in the data in one cell" if you wish and saving that as a CSV file will work as there's only one cell to export so no field delimiters (',') are needed. Getting all the information into 1 cell, adding the pipe delimiters and the ~ line terminator would be a pain, though.

Re: Reverse sign macro

OK

Re: Reverse sign macro

See posts #3 & #6 in this thread

Re: Macro running in every workbook

Something to remember - Bold tags do not work inside of Code Tags...

A 'more correct' way of writing it would be to use

Set wks = ThisWorkBook.Worksheets("Charts")

That way the code can only ever refer to a worksheet in the current workbook, not the Active workbook and will continue to work after you change the filename (Some day, you will).

Re: Pipe "|" Delimiter CSV?

Thank you again, then.

Just to finish off your post, a generic procedure to replace all occurrences of 1 string with another in a file.

Instead of just replacing delimiters it can also be used to replace all occurrences of 'Ford' (for example) with 'Chrysler' - although 'Chrysler Mustang' does not roll off the tongue too easily!

Sub ReplaceCharsInFile(strFileName As String, _
                       strChar1 As String, _
                       strChar2 As String, _
                       Optional BackUp As Boolean = True)




   Dim iFile            As Integer
   Dim FileContents     As String
   Dim iCounter         As Long


   If Dir(strFileName) = vbnullstirng Then
      MsgBox "The file " & UCase(strFileName) & " does not exist...", vbExclamation
      Exit Sub
   End If


   iFile = FreeFile
   Open strFileName For Input As #iFile


   FileContents = Replace(Input(LOF(iFile), #iFile), strChar1, strChar2)
   Close iFile


   If BackUp Then


      Do While Dir(strFileName & "." & Format(iCounter, "000") & ".OLD") <> vbNullString
         iCounter = iCounter + 1
      Loop
      If iCounter < 100 Then
         Name strFileName As strFileName & "." & Format(iCounter, "000") & ".OLD"
      End If


   End If


   Open strFileName For Output As #iFile
   Write #iFile, FileContents
   Close #iFile


End Sub

Optionally, it creates a sequentially numbered backup of the original file.

Re: Pipe "|" Delimiter CSV?

Hardly... I labeled it 'Quick & Dirty' but Thank you.

I will make 1 change. The lines

For j = 1 To UsedColumns - 1 
                Print #1, .Cells(i, j); "|"; 
            Next j 
            Print #1, .Cells(i, UsedColumns)

Writes the values of the cells, but you can use

For j = 1 To UsedColumns - 1 
                Print #1, .Cells(i, j).Text; "|"; 
            Next j 
            Print #1, .Cells(i, UsedColumns).Text

to write the formatted (as displayed) values rather than the underlying values.

Re: Pipe "|" Delimiter CSV?

You can't 'natively' save a file using delimiters other than a comma, tab or space.

You can change your computer settings to use '|' as the text delimiter.

Access can be used to export a table (as a linked table) using the '|' as the delimiter, although you may not have a copy of Access to hand. You can use ADO/DAO to open an Excel file as a database and export that but that means more code and more complication and the worksheet must be a simple list like a database.

As infomage said (posts overlapped), you can save as a 'standard' CSV and edit the file. This can also be automated - again more code and more complication.

The only other alternative is to use VBA to write to a file using '|' as the field delimiter

Sub ExportPipe()


   Dim UsedRows      As Long
   Dim UsedColumns   As Long
   
   Dim i As Long, j As Long
   
   '// Define a suitable file name
   Open "C:\temp\pipedexport.txt" For Output As #1


   With ActiveSheet
   
      UsedRows = .UsedRange.Rows.Count
      UsedColumns = .UsedRange.Columns.Count


      For i = 1 To UsedRows
         For j = 1 To UsedColumns - 1
            Print #1, .Cells(i, j); "|";
         Next j
         Print #1, .Cells(i, UsedColumns)
      Next i
   End With
   
   Close #1
   MsgBox "Finished...", vbInformation


End Sub

(Quick & Dirty)

Re: Create & Save filtered list into new workbook

Misunderstood what you wanted to do with the hidden rows.

Simply comment out/delete the line calling the procedure RemoveHiddenRows. The procedure itself can also be removed

Re: How to Call an Event in Modules

Standard code modules cannot host Events. Any event code for an ActiveX control must be in the code 'module' associated with the object raising the event (Worksheet/Workbook class, 'Standard' class, UserForm).

Shapes or Form Controls are something different. With these you define a Macro to run when the object is clicked (Right click the shape/Assign Macro). This macro must be a public Sub in a standard code module.

It might be useful if you uploaded a copy of your workbook if you cannot work it from that.

[sw]*[/sw]

Re: Can someone please shorten this VBA code for me? Loop code - Should be simple

For Each e In Sheets("Data").Range("A3:J3") 
        If e.Value < 5 Then 
            MsgBox ("Hi") & e.Offset(-1).Value 
        Next e 
    End If

Putting code like that in the Calculate event is not a good idea, IMO. You should explain the logic, there will be a better way.

Re: Insert row after specific text

A simple edit of one of the 'Possible Answers' (insert page break in cell contains specific Text) below.

Sub InsertRows()


   Dim Search           As String
   Dim c                As Excel.Range
   Dim FirstAddress     As String


   Search = "QTY"


   With ActiveSheet.UsedRange


      Set c = .Find(What:=Search, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)


      If Not c Is Nothing Then


         FirstAddress = c.Address
         Do
            c.Offset(1).Insert
            Set c = .FindNext(c)
         Loop While Not c Is Nothing And c.Address <> FirstAddress


      End If


   End With
   
End Sub

Please take care to post in the correct sub-forum. You originally posted this in the 'Newletters' sub-forum but the thread was moved to the 'Excel/VBA Macros' forum which is slightly more relevant to your issue...

Re: Consolidate data from multiple workbooks to one Master Workbook, Code Error

My language skills must be slipping - I didn't ask any questions in this thread. Not one. Oh, OK. Let's be pedantic. I asked 2 rhetorical questions, both answered by myself. But, the issue of me asking questions rather than providing a solution, is made out to be the problem.

Apologies Kenneth, I do not accept this is a 'Language Barrier' - The OP, in my opinion (Note the my opinion, not that of OzGrid, its management, employees, associates or any volunteer, or indeed any other person, registered on this forum), is exactly the sort of poster described in that link.

I will not withdraw that statement. If anyone has a problem with that, then this post will have to be edited to remove it.

The thread has been closed. I don't expect another thread to continue this will be opened.

Re: Copy Row based on value in Other cell

The last 2 lines are redundant and can be removed... or, as they will execute even if 'r' is nothing, they should be removed as they can cause an error to be raised.

Re: VBA environment variables

You need to decide how these users who can save without filling in the data are to be identified.

It could be as simple as asking for a password if the cell(s) are blank when the file is saved or as complex as you can dream up; but, in general, it would be best to avoid any hard-coded lists of users which have to be edited every time someone leaves or is hired.

Re: Consolidate data from multiple workbooks to one Master Workbook, Code Error

Last word on this... At times questions are inevitable because the post is not clear or does not give enough detail about the data. Compare your post #1 with post #1 in this thread Both are vague with no specific information.

Now compare the results when [sensible] questions are answered in a sensible way. Result? Someone has working code, someone else doesn't.

And the infraction is not 'revenge' - it is recorded in a private forum so anyone with the right permissions can see your posting history at a glance.

This, also, is not 'revenge' - I have no interest in trying to resolve your problem for the simple reason that you have never explained it clearly enough. Your 'explanation' in post #5 was simply to refer to post #1 which contains no information other than "if i add more items (up to 5 or 6 items), produced wrong values at "MasterData" Sheet". How you ever expected anyone to answer that without the benefit of seeing the code or your workbook is beyond me.

Quote

if you not able to take challenge please dont waste my time for asking un useful questions

Had to stop myself falling off the chair laughing at that - no one could "take challenge" based on the information in your first post so questions were inevitable.

I also do want want to hear from any other members that you sent them private messages demanding to know why your issue has not been resolved.

Goodbye, but welcome to my Ignore list.

Re: Create & Save filtered list into new workbook

Quick and dirty...

Option Explicit
Private colNames As Collection


Sub Main()
   
   Dim lngItem As Long
   
   '// Stop screen flickering
   Application.ScreenUpdating = False
   
   '// Get unique list of names
   MakeCollection
   
   '// fopr each name...
   For lngItem = 1 To colNames.Count
      
      '// A little feedbar in the Excel Status bar (Bototm left of the Excel window)
      Application.StatusBar = "Processing " & colNames(lngItem) & "..."
      
      '// Copy entire workbook - creates a new workbook without code.
      ThisWorkbook.Sheets.Copy
      
      '// Remove N/A rows. Parameters are Active workbook (The new copy will be active by default),
      '// and the name to keep (All others will be removed).
      RemoveHiddenRows ActiveWorkbook.Sheets("2."), colNames(lngItem)
      
      '// Save to the same directory as this workbook
      With ActiveWorkbook
         .SaveAs Filename:=ThisWorkbook.Path & "\" & colNames(lngItem), AddToMRU:=False, FileFormat:=xlWorkbookDefault
         .Close
      End With
   
   Next
   
   With Application
      .ScreenUpdating = True
      .StatusBar = False
   End With
   
   MsgBox "Finished...", vbInformation
   
End Sub


Private Sub RemoveHiddenRows(ws As Excel.Worksheet, strName As String)


   Dim oRow             As Range
   Dim rng              As Range
   Dim myRows           As Range


   With ws
      '// Make sure the worksheet contains data
      Set myRows = Intersect(.Range("A:A").EntireRow, .UsedRange)
      If myRows Is Nothing Then Exit Sub


      '// Turn off filtering, just in case...
      .AutoFilterMode = False
      
      '// Note explicit references to range - Quick & Dirty to save time
      With .Range("A11:I" & ws.Range("A" & Rows.Count).End(xlUp).Row)
         .AutoFilter
         '// Filter on Col 2 = passed name
         .AutoFilter Field:=2, Criteria1:=strName, Operator:=xlFilterValues


      End With
      
   End With
   
   '// Loop through ll cells in Col 2. If the row is not visible
   '// then add to a range. The entire range is deleted later
   For Each oRow In myRows.Columns(2).Cells
      If oRow.EntireRow.Hidden Then
         If rng Is Nothing Then
            Set rng = oRow
         Else
            Set rng = Union(rng, oRow)
         End If
      End If
   Next


   If Not rng Is Nothing Then rng.EntireRow.Delete
   
   '// And turn off autofiltering
   ws.AutoFilterMode = False
   
End Sub


Private Sub MakeCollection()
   
   Dim r As Excel.Range
   Dim c As Excel.Range
   Dim ws As Excel.Worksheet
   
   Set colNames = New Collection
   Set ws = ThisWorkbook.Sheets("2.")
   
   On Error Resume Next
   
   For Each c In ws.Range(ws.Range("B11"), ws.Range("B" & Rows.Count).End(xlUp))
      colNames.Add c.Text, c.Text
   Next
   
End Sub

All the code goes in a module in the workbook to be copied. It could be speeded up a bit as it is mostly linear code without much optimisation but it seems to work.

Assumptions have been made - the workbooks will be saved in the same directory as the 'parent' workbook; you will get prompted if a file already exists when saving, it will not overwrite.

(The code formatting seems to have an error - that red line compiles OK in Excel)

Re: Consolidate data from multiple workbooks to one Master Workbook, Code Error

Quote

but where is your solution?instead of questions

Please note my earlier comment

Quote

No one is obliged to answer any questions and no question is guaranteed to be answered

I have a pressing engagement in another thread.