[RESOLVED] Enumeration in VB

[simsu]

Hi,

I was wondering if someone could help me with programming the following in VB (VB editor in Excell):

I would like to generate all possible solutions to formulae of the form:

a+b+c < X

so if I have for example the number X=3 (my maximum), it would generate the solutions

a=0, b=0, c=0
a=1, b=0, c=0
a=1, b=1, c=0
etc., until all possible combinations of values for a, b, and c that have a summation below 3 are generated. Also it would be great if each of these solutions would then be exported to a .txt file.

Can anyone help me with the code for this?

[opus]

Dim i as Integer
Dim j as Integer
Dim k as Integer
Dim intMaxValue as Integer
MaxValue = 3
For i=0 to MaxValue
  For j=0 to MaxValue
    For k=0 to MaxValue
      If i + j + k <=MaxValue Then
        'Do whatever you want to each solution
      End If
    Next k
  Next j
Next i

[simsu]

Dim i as Integer
Dim j as Integer
Dim k as Integer
Dim intMaxValue as Integer
MaxValue = 3
For i=0 to MaxValue
  For j=0 to MaxValue
    For k=0 to MaxValue
      If i + j + k <=MaxValue Then
        'Do whatever you want to each solution
      End If
    Next k
  Next j
Next i

Thanks. I will try it later today and see if it does what I want. Just need to find out how i can convert it to .txt or batch files.

[simsu]

Could you please help me with the steps I have to go through in order to get this code working? I have only little knowledge about VB in excell and am running into some problems:

- in the VBA editor where do I input this code and do i need to select any specific settings?
- how do I call upon the procedure (enter the max value and name/number of variables in the summation and see the output of the enumaration procedure?)

Sorry for asking this many questions

[opus]

I'll attach an Excel-file
Put the MaxValue in Cell B1 and start the macro "Enumeration".

[simsu]

Thank you very much! It works brilliantly! Now I only have to export each row containing a solution to a separate .txt file and combine those into a batch file, but I have seen some topics on how to do this while searching the forum. I will try to figure that out myself first. Thanks again!!

[simsu]

This is as far as i got with my exporting to .txt files with the following code (some scraping and adjusting from the internet)

Public Sub ExportToTextFile(FName As String, _
    Sep As String, SelectionOnly 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

Open FName For Output Access Write As #FNum

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).Text
        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

Public Sub DoTheExport()
Dim FName As Variant
Dim Sep As String

FName = Application.GetSaveAsFilename("output", fileFilter:="Text Files (*.txt),*.txt")

If FName = False Then
    MsgBox "You didn't select a file"
    Exit Sub
End If

Sep = InputBox("Enter a single delimiter character (e.g., comma or semi-colon)", _
        "Export To Text File")

ExportToTextFile CStr(FName), Sep, _
    MsgBox("Do You Want To Export The Entire Worksheet?", _
        vbYesNo, "Export To Text File") = vbNo
End Sub

This one allows me to export the whole worksheet or the cells that I have selected to a .txt file. What I would like to do, however, is to save each row on my worksheet to a separte .txt file automatically. Does anyone know how I can adjust this code to do this? Maybe I need other codes, but I am very new to this...

[simsu]

I have some simpler code now that does actually export each row to a separate .txt file (I knew I could do it )

But there is one problem still:

The data in a row is pasted in the .txt file as a colume... how can I adjust the code such that it puts each value in the row to the right (or left) of another value (and not below or above it).

This is the code:

Sub OneRowPerCell()
  Dim newrange As Range
  Dim cell As Range
  Dim filename As Variant
  Dim retVal As Variant
  Dim suffix As String
  suffix = ""
  Dim irow As Long
  Dim row_id As String
  Dim rowRange As Range
  For irow = 1 To ActiveSheet.UsedRange.Rows.Count
       Set newrange = Intersect(Cells.Rows(irow), ActiveSheet.UsedRange)
      row_id = Left(Cells(1, irow).Address(0, 0), _
           Len(Cells(1, irow).Address(0, 0)) - 1)
      filename = "c:\temp\myfile_" & row_id & ".txt"
      If UCase(Left(filename, 4)) = ".HTM" Then suffix = "<br>"
      Close #1   'standard practice, close before opening
      Open filename For Output As 1
      For Each cell In newrange     'newrange
        If Trim(cell.Text) <> "" Then
           Print #1, cell.Text & suffix
        End If
      Next cell
      Close #1
  Next irow
End Sub

[westconn1]

try

Print #1, cell.Text & suffix;

this should print all on the same line, unless you cells contain newlines

[simsu]

try

Print #1, cell.Text & suffix;

this should print all on the same line, unless you cells contain newlines

It does thanks! Is it possible to seperate the text in each cell? E.g. with a space or a comma?

[westconn1]

print#1, cell.text & suffix & " ";'put whatever separator you want between the quotes

[simsu]

print#1, cell.text & suffix & " ";'put whatever separator you want between the quotes

That has solved my problem now I think! THanks for the help you both!