Posts by Jong

programatically

I thought this was a VBA help forum

helping out

This task is relatively easy to do. First you will need to create some kind of macro to perform the task for you.
You can begin by clicking "Tools > Macros > Macros"

Here, specify the name of the macro you want to create, then click "create"

You will be presented with the VB interface. In the text file present, add the following code:

Sub FlagCells()
    Cells.Select
    For Each cell in Selection
         if cell.value = 1 then
             cell.interior.colorindex = 35
         else if cell.value = 0 then
             cell.interior.colorindex = 3
         else
             cell.interior.colorindex = 0
         endif
    Next Cell
end sub

The problem with the above code is that it is not yet optimized for your usage. It checks every possible cell in the worksheet, which will take a few seconds to do. What i would suggest is you add some kind of boudary to make the macro more efficient. For example:

Sub FlagCells()
    Cells.Select
    For Each cell in Selection
         if cell.row = 100 then        'EXIT on row 100
              exit for
         else 
              if cell.value = 1 then
                  cell.interior.colorindex = 35
              else if cell.value = 0 then
                  cell.interior.colorindex = 3
             else
                 cell.interior.colorindex = 0
             endif
    Next Cell
end sub

Above, the code will exit on row 100, making it a lot more efficient. You can change this number to whatever row you think you would go up to.

The easiest way to call this code would now be to click "Tools > Macros > Macros" then select the macro from the list, and click RUN

removing spaces

Removing spaces from a string is quite simple. you can do the following

StringName = Replace(StringName, " ", "")

As for removing carriage returns, I am unsure.

answer

I don't.

But it would be extremely easy to do. Depending on your interface, the inner workings of the application would be quite simple if you run it over excel.

All you would need is some kind of way to have an input. Once the input is received, send the data to the worksheet and save the file.

I suggest having some kind of userform to receive the input. Validate the userform to ensure data is correct, then send it off to the spreadsheet. You could also create a userform to view the data, but that would be redundant. If you format the worksheet nicely, all the data can be viewed directly.

If you have any specific questions regarding the project, just ask.

answer

Ok, I'm not sure if i completely understand your question, but the following chunk of code will eliminate any blank cells by moving data to the first available row.
-----------------------

Dim RowPtr as integer
Dim RowFinder as integer
RowPtr = 1


'Get first available cell (first blank cell)
While Worksheets("Worksheetname").Range("A" & RowPtr) <> ""
     RowPtr = RowPtr + 1
wend


'Rowfinder will become the first blank row
RowFinder = RowPtr


While RowFinder <> 9999  'MAX COUNT THAT YOU WANT
     'Find the next row with data
     While Worksheets("Worksheetname").Range("A" & RowFinder) = ""    'A can be any column
          RowFinder = RowFinder + 1
     wend


     'cut the original, and paste it at its new location
     Worksheets("Worksheetname").Range("A" & RowPtr) = Worksheets("Worksheetname").Range("A" & RowFinder)
     Worksheets("Worksheetname").Range("A" & RowFinder) = ""




     RowPtr = RowPtr + 1
 
Wend

Problem

Looks like to me that your not specifying the worksheet for the range.

for example:

Worksheets("MyWorksheet").Activate
Range("B2").Select
With ActiveCell
.FormulaR1C1 = "=PROPER(TRIM(RC[-1]))"
End With
Range("B2").AutoFill Destination:=Worksheet("MySheet").Range("B2:B" & Range("B65536").End(xlUp).Row)

ahh

Iv'e never worked with scenarios before.

Hopefully someone will be able to inform us on how to invoke a scenario from VBA.

Forms

The only "easy way" to get this to work is to get someone to do it for you

When you say "Scenario", do you mean this Sub/Functions?
If you have all your scenarios devided in their seperate functions, I could paste some code to show you an example.

suggestion

I suggest you create a UserForm with some kind of selection method for your scenarios. If your scenarios are dynamic, you should use a ListBox and populate it dynamically.
If you have a fixed set of scenarios, and a large amount, use a DropDownList.
If you have a fixed set of scenarios, and a small amount, use CheckBox or RadioButton selection methods.

If you need help with the code part of this, let us know.
The Form can be created quite easily by using drag&Drop interface in VB.

Version

What version of VB are you using.

I can use the Range variable in 6.3

Sub

Yes, I'm sorry I overlooked that. Here is the subfunction, in fact its the most complicated part of it.. The code below is pasted right out of my project, so you might have to modify it a bit. It will basically take the text out of the txt file, and parse it to the specified Workbook/Worksheet.

To be quite honest, I suggest you write your own routine, because this will actually get the data, and format it the way I wanted it. There is also another sub routine inside this one called "ParseDelimitedStrings" which does just that... I also included it below. I hope it helps out anyways.
-------------------------------

Sub ImportRangeFromDelimitedText(SourceFile As String, SepChar As String, _
TargetWB As String, TargetWS As String, TargetAddress As String)

Dim SC As String * 1, TargetCell As Range, TargetValues As Variant
Dim r As Long, fLen As Long
Dim fn As Integer, LineString As String
' validate the input data if necessary
If Dir(SourceFile) = "" Then Exit Sub ' SourceFile doesn't exist
If UCase(SepChar) = "TAB" Or UCase(SepChar) = "T" Then
SC = Chr(9)
Else
SC = Left(SepChar, 1)
End If

' perform import
Workbooks(TargetWB).Activate
Worksheets(TargetWS).Activate
Set TargetCell = Range(TargetAddress).Cells(1, 1)
On Error GoTo NotAbleToImport
fn = FreeFile
Open SourceFile For Input As #fn
On Error GoTo 0
fLen = LOF(fn)
r = 0
While Not EOF(fn)
Line Input #fn, LineString
If r Mod 100 = 0 Then
Application.StatusBar = "Reading data from " & _
SourceFile & " " & _
Format(Seek(fn) / fLen, "0 %") & "..."
End If
TargetValues = ParseDelimitedString(LineString, SepChar)
UpdateCells TargetCell.Offset(r, 0), TargetValues
r = r + 1
Wend
Close #fn
Application.Calculation = xlCalculationAutomatic
NotAbleToImport:

' clean up
Set TargetCell = Nothing 'This is project specific
Application.StatusBar = False 'This is project specific
End Sub

--------------------------------------

Function ParseDelimitedString(InputString As String, SC As String) As Variant

Dim i As Integer, tString As String, tChar As String * 1
Dim sCount As Integer, ResultArray() As Variant
tString = ""
sCount = 0
For i = 1 To Len(InputString)
tChar = Mid$(InputString, i, 1)
If tChar = SC Then
sCount = sCount + 1
ReDim Preserve ResultArray(1 To sCount)
ResultArray(sCount) = tString
tString = ""
Else
tString = tString & tChar
End If
Next i
sCount = sCount + 1
ReDim Preserve ResultArray(1 To sCount)
ResultArray(sCount) = tString
ParseDelimitedString = ResultArray
End Function

File Open Dialog

Iv'e done a similar application, although mine was more complicated. I imported a TXT file from a machine that extracts data from samples taken from nature. The TXT files that were imported are about 500K in size. To open these files, I did the following (Open file dialog):
-----------------------------------------------------
Sub OpenFile()
Dim s As String

s = Application.GetOpenFilename("Machine Files (*.txt),*.txt", _
1, "Opens the machine file", , False)
ImportRangeFromDelimitedText s, _
",", ThisWorkbook.Name, "Extracted", "A1"
End Sub

'Note: Extracted is the worksheet where the data is pasted
----------------------------------------------------

The above code will allow you to incorporate the "Browse" dialog you are trying to do. And then the text can be parsed into a worksheet of your choice.

Hope this helps out.

Selected

What you can do is when you do invoke your macro, the selected cells can be access doing the following:

Worksheets("MySheet").Activate 'Activate the sheet with the selection
ActiveSheet.Selection.Foo

With cells, you can do whatever you need to do in VBA to the cells.
You can also save the Range into a Range variable:

Dim myRange as Range

myRange = ActiveSheet.Selection