Store a specific column value until next value is identified

[urskrishna18]

I have very basic knowledge in VBA, I have one requirement where the data for a specific set will be available in multiple rows and it will be like Header (ex. from A to D) and lines (ex. from E:J). Header will not be there for all the line rows, so when validating the line data with Header data, I need to validate the value in for ex. col "D2" with all the lines and when the new header is available, follow the same pattern again.

Below is the sample data format, in which col "A:D" represents Header data and from col "E:J" represents line data. Through out the line validation with Header, I want col "D" value to be captured through out that iteration.

Ex. When the "ABC" value is iterated, "PO1" should be available for validation for rows "E2:J4". After this iteration and when the new value on Col A, i.e "DEF" is encountered then the value in Col "D" to be changed like "PO2" from the next 2 rows of the Header.

As I have very basic knowledge in VB, not sure how I can achieve this. Can anyone help me here please?

I tried to compare the first value with the next available value in Col A, during this iteration tried storing the Col "D" value to a global variable but it is not giving me expected results

Sample code that i tried:

Public v As Integer

Sub inv()

Dim i As Integer, j As Integer, temp As Integer, rng As Range
Dim lastRow As Integer, lastRowSheet2 As Integer
Dim sheet1 As Worksheet, Sheet2 As Worksheet

Set sheet1 = Sheets("Data")
Set Sheet2 = Sheets("res")
lrow = sheet1.Range("A1").SpecialCells(xlCellTypeLastCell).Row


For i = 2 To lrow
    
    If sheet1.Range("A" & i).Value <> "" Then
        invv = sheet1.Range("A" & i).Value
        v = i

    End If
    If sheet1.Range("A" & i + 1).Value <> "" Then
        ninv = sheet1.Range("A" & i + 1).Value

    End If

    If invv <> ninv And sheet1.Range("A" & i + 1).Value <> "" Then
    
        Sheet2.Range("A" & i).Value = sheet1.Range("D" & v).Value
        MsgBox "Alert -Entry in row is not equal to Previous Cell !!"
        
        'Exit Sub
    End If

Next i



End Sub

 

[Legacy 143009]

Sub myFunction()
  Dim lRow As Long, iRow As Long, eRow As Long
  lRow = Cells(Rows.Count, 5).End(xlUp).Row
  
  For i = 2 To lRow
    If Cells(i, 1).Value <> "" Then
      iRow = Range("A" & i).End(xlDown).Row - 1
      If iRow > lRow Then iRow = lRow
      For j = i To iRow
        'Do what ever you want with Cells(i, 4).Value :)
      Next
    End If
  Next
End Sub

For example, let's fill all rows with Column D value:

Sub myFunction()
  Dim lRow As Long, iRow As Long, eRow As Long
  lRow = Cells(Rows.Count, 5).End(xlUp).Row
  
  For i = 2 To lRow
    If Cells(i, 1).Value <> "" Then
      iRow = Range("A" & i).End(xlDown).Row - 1
      If iRow > lRow Then iRow = lRow
      For j = i To iRow
        Cells(j, 4).Value = Cells(i, 4).Value
      Next
    End If
  Next
End Sub

 

[urskrishna18]

Sub myFunction()
  Dim lRow As Long, iRow As Long, eRow As Long
  lRow = Cells(Rows.Count, 5).End(xlUp).Row
 
  For i = 2 To lRow
    If Cells(i, 1).Value <> "" Then
      iRow = Range("A" & i).End(xlDown).Row - 1
      If iRow > lRow Then iRow = lRow
      For j = i To iRow
        'Do what ever you want with Cells(i, 4).Value :)
      Next
    End If
  Next
End Sub

For example, let's fill all rows with Column D value:

Sub myFunction()
  Dim lRow As Long, iRow As Long, eRow As Long
  lRow = Cells(Rows.Count, 5).End(xlUp).Row
 
  For i = 2 To lRow
    If Cells(i, 1).Value <> "" Then
      iRow = Range("A" & i).End(xlDown).Row - 1
      If iRow > lRow Then iRow = lRow
      For j = i To iRow
        Cells(j, 4).Value = Cells(i, 4).Value
      Next
    End If
  Next
End Sub

@Flashbond, Thank you for the quick response. I think this gave me the result that i am looking for and an idea on how to take care of the other logic, as this is a piece of code in another script. Thanks for your help

 

[Legacy 143009]

Glad it gave you an idea