Help with loop..

[kragster]

Hi,
I have a bit of trouble understanding a part of this loop, so if anyone would be so kind to translate it, it would be much appreciated.

Dim AssayRowIns
    AssayRowIns = 4
    Do Until Range("A" & AssayRowIns) = ""
        Rows("" & AssayRowIns & ":" & AssayRowIns + 6 & "").Insert Shift:=xlDown
        AssayRowIns = AssayRowIns + 8
    Loop

What does this do in the loop?

Rows("" & AssayRowIns & ":" & AssayRowIns + 6 & "").Insert Shift:=xlDown

 

[mikej336]

It confused me a little also...

'Start on the 4 row from the top
AssayRowIns = 4

'to this as long as somthing is in the A Column
Do Until Range("A" & AssayRowIns) = ""

'Insert 6 rows starting at row 4
Rows("" & AssayRowIns & ":" & AssayRowIns + 6 & "").Insert Shift:=xlDown

'add 8 rows to the current posision starting at 4
'this essentially skips 1 row after the 6 inserted.
AssayRowIns = AssayRowIns + 8

Loop

Hope this helps

Mike

 

[mikej336]

PS.

'the double quotes are not neccessary.
Rows("" & AssayRowIns & ":" & AssayRowIns + 6 & "").Insert Shift:=xlDown

'this should work fine
Rows(AssayRowIns & ":" & AssayRowIns + 6).Insert Shift:=xlDown

'on the first line, 4 it would translate to
Rows(4 & ":" & 4 + 6).Insert Shift:=xlDown

Mike

 

[kragster]

Thanks Mike. I'm trying to edit another persons VBA project, but I have a hard time figuring out where he loads his data from into some specific cells. This means that this loop only inserts empty rows, doesn't populate them with data. *sighs*

I'm trying to edit another persons VBA program, but I have a hard time figuring out where he loads some specific data. One spreadsheet draws raw data from an oracle database and from that sheet he generates new reportsheets using VBA.

 

[PHV]

Why not debug step by step the VBA project ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kragster]

PHV: I'm kinda new to programming so you would have to explain to me how that is done ;-)

 

[kragster]

I have a huge nested loop in which I am trying to find out where it gets the legend data from. Instead of the current legend data I would like it to use Range("B" & datarow), thus B3 the first loop and B11 the second and so forth.

here is the loop:

    datarow = 3
    Do Until Range("A" & datarow) = ""
        'Summary data header formatting
        Range("B" & datarow - 1) = "Readout"
        Range("C" & datarow - 1) = "Range"
        Range("D" & datarow - 1) = "IC50[µM]"
        Range("D" & datarow).NumberFormat = "0.000"
        Range("E" & datarow - 1) = "Max"
        Range("E" & datarow).NumberFormat = "0"
        Range("F" & datarow - 1) = "Min"
        Range("F" & datarow).NumberFormat = "0"
        Range("G" & datarow - 1) = "Curve"
        Range("H" & datarow - 1) = "Cells"
        Range("I" & datarow - 1) = "Stimulus"
        Range("J" & datarow - 1) = "Time"
        Range("K" & datarow - 1) = "LAD: "
        Range("L" & datarow - 1) = "Comment: "
        Range("B" & datarow - 1 & ":L" & datarow - 1).Font.Bold = True
        Columns("B:L").ColumnWidth = 10
        'Curve types X,Z and H
        If Range("F" & datarow) = "Z" Then
            Range("C" & datarow & ":E" & datarow & ",G" & datarow) = ""
        ElseIf Range("F" & datarow) = "X" Then
            Range("C" & datarow & ":E" & datarow & ",G" & datarow) = ""
        ElseIf Range("F" & datarow) = "H" Then
            Range("C" & datarow & ":E" & datarow & ",G" & datarow) = ""
        End If
        
        'Concentrations in µM
        
        Range("M" & datarow & ":U" & datarow).Cut
        ActiveSheet.Paste Destination:=Range("B" & datarow + 1)
        Range("A" & datarow + 1) = "Conc.(µM)"
        'Concentrations in log M
        Range("A" & datarow + 2) = "log(conc)[M]"
        Range("B" & datarow + 2).Formula = "=Log(B" & datarow + 1 & "*10^-6)"
        'Debug.Print Range("B" & DataRow + 2)
        Range("C" & datarow + 2).Formula = "=Log(C" & datarow + 1 & "*10^-6)"
        Range("D" & datarow + 2).Formula = "=Log(D" & datarow + 1 & "*10^-6)"
        Range("E" & datarow + 2).Formula = "=Log(E" & datarow + 1 & "*10^-6)"
        Range("F" & datarow + 2).Formula = "=Log(F" & datarow + 1 & "*10^-6)"
        Range("G" & datarow + 2).Formula = "=Log(G" & datarow + 1 & "*10^-6)"
        Range("H" & datarow + 2).Formula = "=Log(H" & datarow + 1 & "*10^-6)"
        Range("I" & datarow + 2).Formula = "=Log(I" & datarow + 1 & "*10^-6)"
        Range("J" & datarow + 2).Formula = "=Log(J" & datarow + 1 & "*10^-6)"
        
        Range("B" & datarow + 2 & ":J" & datarow + 2).Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlValues
        
        Range("B" & datarow + 2 & ":J" & datarow + 2).NumberFormat = "0.0"
        Range("A" & datarow + 2 & ":J" & datarow + 2).Font.FontStyle = "Bold"
        'PCTACT
        Range("V" & datarow & ":AD" & datarow).Cut
        ActiveSheet.Paste Destination:=Range("B" & datarow + 3)
        Range("A" & datarow + 3) = "PCTACT"
        Range("B" & datarow + 3 & ":J" & datarow + 3).NumberFormat = "0"
        'Standard deviation
        Range("AE" & datarow & ":AM" & datarow).Cut
        ActiveSheet.Paste Destination:=Range("B" & datarow + 4)
        Range("A" & datarow + 4) = "SD"
        Range("B" & datarow + 4 & ":J" & datarow + 4).NumberFormat = "0.0"
        'n
        Range("AN" & datarow & ":AV" & datarow).Cut
        ActiveSheet.Paste Destination:=Range("B" & datarow + 5)
        Range("A" & datarow + 5) = "n tests"
        'Removing data if n<2
        Dim n As Integer
        n = 2
        Do Until n = 11
            If Cells(datarow + 5, n) < 2 Then
                Cells(datarow + 4, n) = ""
            End If
            If Cells(datarow + 5, n) = 0 Then
                Cells(datarow + 3, n) = ""
            End If
            n = n + 1
        Loop
        'Excluded status
        Range("AW" & datarow & ":BE" & datarow).Cut
        ActiveSheet.Paste Destination:=Range("P" & datarow)
        Range("O" & datarow) = "Excluded"
        'Range("O" & DataRow & ":X" & DataRow).Font.ColorIndex = 2
        'AssayId with conditions generation
        Range("O" & datarow - 1) = Range("A" & datarow) & ", " & Range("B" & datarow) & ", " & _
        Range("H" & datarow) & ", " & Range("I" & datarow) & ", " & Range("J" & datarow)
        'Inserts "InChart" if it should be included
        SheetName = ActiveSheet.Name
        If Not Worksheets(SheetName).Range("B" & datarow + 3) = "" Then
            Worksheets(SheetName).Range("O" & datarow + 1) = "InChart"
        End If
        'Data for fit curve
        If Not Range("C" & datarow) = "" Then 'is empty if curvetype = X,Z or H
            'Generation of 40 X values for curve fit
            Dim ValueIncrem, LogHighConc, LogLowConc
            LogHighConc = Range("J" & datarow + 2) + 0.5
            LogLowConc = Range("B" & datarow + 2) - 0.5
            ValueIncrem = (LogHighConc - LogLowConc) / 40
            n = 1
            Cells(datarow + 2, n + 24) = "fitX"
            Do Until n = 41
                Cells(datarow + 2, n + 25) = LogLowConc + n * ValueIncrem
                n = n + 1
            Loop
            'Generation of 40 Y values for curve fit
            Dim fitMin_A, fitMax_B, fitEC50_C, fitHill_D
            fitMin_A = Range("E" & datarow)
            fitMax_B = Range("D" & datarow)
            fitEC50_C = Range("C" & datarow)
            fitHill_D = Range("G" & datarow)
            n = 1
            Cells(datarow + 3, n + 24) = Cells(datarow, 1) & "-fitY"
            Dim FormulaY
            Do Until n = 41
                FormulaY = fitMin_A + ((fitMax_B - fitMin_A) / (1 + ((fitEC50_C / (10 ^ 6 * 10 ^ Cells(datarow + 2, n + 25))) ^ fitHill_D)))
                '205: y =         A + ((       B -        A) / (1 + ((        C /          x                                 )^         D)))
                Cells(datarow + 3, n + 25) = FormulaY
                n = n + 1
            Loop
        End If
        Range("B" & datarow - 1 & ":J" & datarow + 6).HorizontalAlignment = xlCenter
        
       'Assay name for chart legend
       'Cells(datarow - 1, 66) = Right((Cells(datarow, 1)), Len(Cells(datarow, 1)) - 5)
       Cells(datarow, 1) = VBA.Right((Cells(datarow, 1)), Len(Cells(datarow, 1)) - 5)
       datarow = datarow + 8
    Loop

 

[kragster]

Can anyone explain to me what this does in the loop?

Cells(datarow, 1) = VBA.Right((Cells(datarow, 1)), Len(Cells(datarow, 1)) - 5)

Any help is much appreciated.

 

[rmikesmith]

The Right function syntax is
Right(String As String, Len As Long)

This function returns the right-most Len characters from a String. It comes in two varieties: Right (returns a Variant) and Right$ (returns a String). I always use the string version, which is somewhat faster. The VBA qualifier is probably not strictly needed here. Some functions have a VBA and an Excel equivalent with the same name. The VBA qualifier would prevent ambiguity.

In your code, this is eliminating the first 5 characters in the value of Cells(datarow, 1)


Hope this helps.
Mike

 

[kragster]

Thanks Mike. It makes sense now!

 

[PHV]

A simpler way:
Cells(datarow, 1) = Mid$(Cells(datarow, 1), 6)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886