Sleeper: If and Statement based on one cell and

[mark jones]

I am trying to find a solution to If cell is >1 and the cell below is blank (""), then insert formula into specific cell location.

Sub InsertSlopeFormula()
Dim Cell As Range
For Each Cell In Range("C1:C115")
If Cell.Value > 1 And Cell.Value.Offset(1, 0) = "" Then
Cell.Value.Offset(2, 0) = "a"
End If
Next Cell
End Sub

[p45cal]

Sub InsertSlopeFormula()
Dim Cell As Range

For Each Cell In Range("C1:C115")
If Cell.Value > 1 And Cell.Offset(1).Value = "" Then Cell.Offset(2).Value = "a"
Next Cell
End Sub

[mark jones]

Sub InsertSlopeFormula()
Dim Cell As Range

For Each Cell In Range("C1:C115")
If Cell.Value > 1 And Cell.Offset(1).Value = "" Then Cell.Offset(2).Value = "a"
Next Cell
End Sub



p45cal,

Thanks for that. I am certainly closer to success now!!! The code works except when i have multiple blanks as it continues to see data in the loop. Please see below and share any thoughts as I am looking for a solution to only fill in the second blank row it comes to with a formula.



10796414.8


10796414.8


10796414.8


10796414.8


10796414.8


10796414.8


10796414.8


10796414.8





a





a





a





a





a

[Aussiebear]

Sorry Mark Jones but your example doesn't explain much in its current layout. Try attaching a workbook with some sample data.

[mark jones]

AussieBear,

thanks for the reply. i have attached the workbook.

[mark jones]

AussieBear,

thanks for the reply. i have attached the workbook.

[Aussiebear]

Man, you know how to make it complicated. The workbook you posted has two sheets namely;
Sheet1 called "wytheville vba", which contains data ( assuming its GPS points), which fills the range A0:DD25865; and
Sheet2 called "Sheet1", which contains similar data with repeating patterns of data namely 13 rows of data then 4 blank rows.

Are you intending to run the sub on Sheet2 only?

is it also your intention to fill the cell C15 with the value "a"?

Right now the sub you tried to write and has since been corrected by P45cal, will keep looping down column c until the active cell's value is greater than 1 and the cell below the active cell's value is blank, which doesn't come true until it reached cell C13, other wise it keeps looping down the column until it finds the condition true again.

There is no exit strategy once it runs out of data.

[arnelgp]

Sub InsertSlopeFormula()
Dim Cell As Range
Dim i As Integer
For i = 1 To 115
Set Cell = Sheet1.Cells(i, 3) 'NOTE, replace Sheet1 with correct sheet number
If Val(Cell & "") > 1 And Cell.Offset(1, 0) = "" Then
If Cell.Offset(2, 0) <> "a" And Val(Cell.Offset(2, 0) & "") = 0 Then
Cell.Offset(2, 0) = "a"
End If
End If
Next i
End Sub

[p45cal]

Instead of showing us code which doesn't do what you want and expect us to change it to do what you want (which we don't know), why don't you just explain what you want it to do?
A guess that you want it to operate on the sheet Sheet1, try the following (again a guess) with that sheet being the active sheet when you run it:


Sub blah()
For Each are In Cells.SpecialCells(xlCellTypeConstants, 23).Areas
If are.Rows.Count > 1 And are.Columns.Count > 3 Then
Set myCell = are.Cells(are.Rows.Count, 3).Offset(2)
If myCell.Offset(1).Value = "" And myCell.Value = "" Then
myCell.Value = "a"
End If
End If
Next are
or the same thing:
Sub blah2()
For Each are In Cells.SpecialCells(xlCellTypeConstants, 23).Areas
If are.Rows.Count > 1 And are.Columns.Count > 3 Then
With are.Cells(are.Rows.Count, 3).Offset(2)
If .Offset(1).Value = "" And .Value = "" Then .Value = "a"
End With
End If
Next are
End Sub

[mark jones]

p45cal,

Point well taken. I have attached an additional workbook ("wytheville finished") showing the desired end product. Aussiebear is correct that these are gps points. The sheet I am working on is a small sample of what are sometimes 100k plus points. As you can see from the finished sheet, the next step is to calculate the slope % from one first point to last point to allow for corrections to Range "D". Range "D" is the actual ground elevation. I can then calculate what the actual elevation should be in Range "E". Lastly, Range "F" lets me see the slope between two sets of points looking East to West. Any further assistance would be greatly appreciated. :bow::bow::bow:

[p45cal]

I'm guessing that you want to start with a sheet like Start in the attached.
I've made a copy of that sheet (Start (2)) to experiment on, select that sheet then run the macro blah (Alt+F8 on the keyboard then-double click on the blah macro).
Compare with Sheet1.

The macro:
Sub blah()
For Each are In Cells.SpecialCells(xlCellTypeConstants, 23).Areas
If are.Rows.Count > 1 And are.Columns.Count > 3 Then
Set mycell = are.Cells(are.Rows.Count, 3).Offset(2)
If mycell.Offset(1).Value = "" And mycell.Value = "" Then
mycell.Formula = "=(" & are.Cells(are.Rows.Count, 4).Address & "-" & are.Cells(1, 4).Address & ")/(" & are.Cells(are.Rows.Count, 2).Address & "-" & are.Cells(1, 2).Address & ")"
fr = mycell.Row
lr = are.Row + are.Rows.Count - 1
are.Columns(4).Offset(, 1).FormulaR1C1 = "=RC[-1]-(((RC[-3]-R" & lr & "C[-3])*R" & fr & "C[-2])+R" & lr & "C[-1])"
If are.Column > 6 Then are.Columns(4).Offset(, 2).FormulaR1C1 = "=(RC[-2]-RC[-8])/(RC[-3]-RC[-9])"
End If
End If
Next are
End SubIt's a start.
Lots of things could make it go wrong.

[mark jones]

p45cal,

Thank you for your time. I will get this loaded and see where it takes me....

[mark jones]

If and Statement based on reference to 1st cell - Stack Overflow (https://stackoverflow.com/questions/76085559/if-and-statement-based-on-reference-to-1st-cell)

[Aussiebear]

I arrived at a different slope factor than P45cal, however the end results are the same.

[mark jones]

Thanks for your work on this. I have ran the code for several days and it seems to be functioning flawlessly. Due to size of data across columns that will occur in these layouts, I need to have a dynamic range for the columns rather than fixed. I started below, but cannot get past the range plugin. Any thoughts?


Sub Vlookup()
Dim Col As Integer
Dim Row As Integer
Dim LastRow As String
Dim LastColumn As String
LastRow = Worksheets("Start").UsedRange.Rows.Count
LastColumn = Worksheets("Start").UsedRange.Columns.Count
For Col = 2 To LastColumn Step 6
Application.WorksheetFunction.Vlookup.Offset(0, -1), _
Sheets("wytheville vba").Range("A1:D25864"), 2, True)
Next Col
End Sub

[p45cal]

Due to size of data across columns that will occur in these layouts, I need to have a dynamic range for the columns rather than fixed.
Not sure what this means. The code I provided acts on the entire active sheet. The line:

For Each are In Cells.SpecialCells(xlCellTypeConstants, 23).Areas
does the equivalent of pressing F5 on the keyboard when a single cell is selected and choosing the Special… button:

30760

then choosing as shown below and clicking OK.

30761

If you do this you'll see the blocks that it looks at; it checks each block to ensure it has more than 1 row and more than 3 columns before possibly acting on that block.
This assumes the numbers etc. on the sheet are NOT formulas, but plain values.
The only thing hard-coded is the 6 in line beginning:

If are.Column > 6 …
which stops the second column of calculations being added to the block of cells if that block's leftmost column is before column 6 (Column F).

So I don't understand your need for a dynamic range.