Rows.Count).End(xlUp).Row

chipsworld

Board Regular
Joined
May 23, 2019
Messages
120
Not sure where this is wrong , but...

The below only puts new data on Row 35. If Row 35 has data, it will simply write over it. Where am I wrong? This is making me crazy!
Currently there is data on Rows 2 and 3 and then nothing until 35. Row 1 is the header Row...

VBA Code:
Public Sub PDMRA_Copy()
Dim rw As Long
Dim DestWS As Worksheet
Dim smname As String
    
smname = frmPDMRAcalc.txtnam2.Value
    
    Set DestWS = Sheets("PDMRA History")
    On Error Resume Next
    With DestWS
        rw = .Range("A:A").Find(smname, LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlPrevious).Row
        If rw <> 0 Then
            .Range("B" & rw).Value = frmPDMRAcalc.txtqmobsrt.Value
            .Range("C" & rw).Value = frmPDMRAcalc.txtqmobend.Value
            .Range("D" & rw).Value = frmPDMRAcalc.cmbqcomp.Value
            .Range("E" & rw).Value = frmPDMRAcalc.cmbqusc.Value
            .Range("F" & rw).Value = frmPDMRAcalc.cmbqcntry.Value
            .Range("G" & rw).Value = frmPDMRAcalc.txtcmobsrt.Value
            .Range("H" & rw).Value = frmPDMRAcalc.txtcmobend.Value
            .Range("I" & rw).Value = frmPDMRAcalc.cmbccomp.Value
            .Range("J" & rw).Value = frmPDMRAcalc.cmbcusc.Value
            .Range("K" & rw).Value = frmPDMRAcalc.cmbccntry.Value
            .Range("L" & rw).Value = frmPDMRAcalc.txtmonthsdwell.Value
            .Range("M" & rw).Value = frmPDMRAcalc.txtpdmraern.Value
            .Range("N" & rw).Value = frmPDMRAcalc.txtmnthsearned.Value
            .Range("O" & rw).Value = Sheets("Formulas").Range("AC16").Value
            .Range("P" & rw).Value = Sheets("Formulas").Range("AC17").Value
        Else
            rw = Range("A" & Rows.Count).End(xlUp).Row + 1
            .Range("A" & rw).Value = frmPDMRAcalc.txtnam2.Value
            .Range("B" & rw).Value = frmPDMRAcalc.txtqmobsrt.Value
            .Range("C" & rw).Value = frmPDMRAcalc.txtqmobend.Value
            .Range("D" & rw).Value = frmPDMRAcalc.cmbqcomp.Value
            .Range("E" & rw).Value = frmPDMRAcalc.cmbqusc.Value
            .Range("F" & rw).Value = frmPDMRAcalc.cmbqcntry.Value
            .Range("G" & rw).Value = frmPDMRAcalc.txtcmobsrt.Value
            .Range("H" & rw).Value = frmPDMRAcalc.txtcmobend.Value
            .Range("I" & rw).Value = frmPDMRAcalc.cmbccomp.Value
            .Range("J" & rw).Value = frmPDMRAcalc.cmbcusc.Value
            .Range("K" & rw).Value = frmPDMRAcalc.cmbccntry.Value
            .Range("L" & rw).Value = frmPDMRAcalc.txtmonthsdwell.Value
            .Range("M" & rw).Value = frmPDMRAcalc.txtpdmraern.Value
            .Range("N" & rw).Value = frmPDMRAcalc.txtmnthsearned.Value
            .Range("O" & rw).Value = Sheets("Formulas").Range("AC16").Value
            .Range("P" & rw).Value = Sheets("Formulas").Range("AC17").Value
        End If
    End With
    
End Sub
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,587
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Where do you want the data? row 36 or row 4?
 

chipsworld

Board Regular
Joined
May 23, 2019
Messages
120
every new entry should go to the first empty row from the top...

It works fine for a name that is already there. Finds the row and copies data and overwrites the current.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,587
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Sorry you are confusing me now. You run the first code which is pasting at every 7th row, now you saying that the 2nd code is to paste at the first first empty row from the top.

Please describe from start to finish in detail exactly what you want.
 

chipsworld

Board Regular
Joined
May 23, 2019
Messages
120

ADVERTISEMENT

Mark,
Now you have me confused! LOL The goal here is to always paste at the first empty row below any filled rows if new data.

1. The first code does a lookup for the name, and if it finds a match, it pastes the updated data through an overwrite. (works perfectly)
2. The second code (else) is if a match is not found, and it pastes all data to the first empty row. Thereby adding a new row of data. (pastes data on row 35 - always)

How is the first code pasting every 7th row?

VBA Code:
rw = .Range("A:A").Find(smname, LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlPrevious).Row ' finds name in column A if present and returns row

VBA Code:
rw = Range("A" & Rows.Count).End(xlUp).Row + 1 ' if name not found in column A finds first empty row by searching from the bottom up and places data on that row.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,587
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Oops my bad, posted on the wrong thread, sorry.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,587
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

To answer your question I think it is due to you missing a period in front of your range

Rich (BB code):
rw = .Range("A" & Rows.Count).End(xlUp).Row + 1
 
Solution

chipsworld

Board Regular
Joined
May 23, 2019
Messages
120
Brilliant! I have been staring at that code for three days trying to figure out where I goofed up.

Thank you!!!
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,587
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Happy I could help.... eventually :rolleyes:
 

Watch MrExcel Video

Forum statistics

Threads
1,118,134
Messages
5,570,364
Members
412,321
Latest member
Yusuf_A
Top