Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Macro for priclist

  Asked By: Everett    Date: Nov 02    Category: MS Office    Views: 660
  

I have the following macro that works up to where I want to insert in A if
the cell in A is blank the value that is in H one row down (ex in A1 the
Value in H2)

Range("A1:N473").Copy
Sheets("Cust price list").Select
Range("A7").PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Columns("B:B").EntireColumn.Hidden = True
Columns("D:E").EntireColumn.Hidden = True
Columns("G:G").EntireColumn.Hidden = True
Columns("N:N").Delete Shift:=xlToLeft
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
For row_index = lastrow - 1 To 2 Step -1
If Cells(row_index, "H").Value <> _
Cells(row_index + 1, "H").Value Then
Cells(row_index + 1, "H").EntireRow.Insert _
(xlShiftDown)
End If
Next
Dim RowNdx As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For RowNdx = lastrow To 1 Step -1
With Cells(RowNdx, "A")
If .Value = "" Then
.Value = Cells(row_index + 1, "H")
End If
End With
Next RowNdx


Still a bit inexperienced but getting there.

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Chelsea Rose     Answered On: Nov 02

I have the following macro  that works  up to where I want to insert  in A if
the cell  in A is blank  the value that is in H one row  down (ex in A1 the
Value in H2)

Range("A1:N473").Copy
Sheets("Cust price list").Select
Range("A7").PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Columns("B:B").EntireColumn.Hidden = True
Columns("D:E").EntireColumn.Hidden = True
Columns("G:G").EntireColumn.Hidden = True
Columns("N:N").Delete Shift:=xlToLeft
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
For row_index = lastrow - 1 To 2 Step -1
If Cells(row_index, "H").Value <> _
Cells(row_index + 1, "H").Value Then
Cells(row_index + 1, "H").EntireRow.Insert _
(xlShiftDown)
End If
Next
Dim RowNdx As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For RowNdx = lastrow To 1 Step -1
With Cells(RowNdx, "A")
If .Value = "" Then
.Value = Cells(row_index + 1, "H")
End If
End With
Next RowNdx


Still a bit  inexperienced but getting there.

 
Didn't find what you were looking for? Find more on Macro for priclist Or get search suggestion and latest updates.




Tagged: