ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   How can I make this macro works (https://www.excelbanter.com/excel-programming/426319-how-can-i-make-macro-works.html)

AnnaC
How can I make this macro works
 
This is a code that Joel so kindly wrote for me, I add some range and delete
some, but now I can not make it work right. I want to update the previous
data using the data entry sheet, but I cant make it write all the data from
the (the cells next to the ID) row to the data entry sheet (Entry) or send
the data back to the data sheet (data). Would somebody mind helping me with
this?

Sub GetData()
Set EntrySht = Sheets("Input")
Set DataSht = Sheets("Data")

ID = EntrySht.Range("Q2")

'See if ID already exists
Set c = DataSht.Columns("B").Find(what:=ID, LookIn:=xlValues,
lookat:=xlWhole)
If c Is Nothing Then
'clear Entry Sheet
EntrySht.Range("Q2").ClearContents
Else
'move old data from data sheet to entry sheet
With DataSht
DataRow = c.Row

'to entry sheet
EntrySht.Range("E5,I5,M5,C7,I7,M7,C7,C9,F9,I9,M9,E 11,F19,D21,D26,D33,C36")
= .Range("C" & c.Row)
End With
End If


End Sub
Sub Submit()
Set EntrySht = Sheets("Input")
Set DataSht = Sheets("Data")

ID = EntrySht.Range("Q2")

'See if ID already exists
Set c = DataSht.Columns("B").Find(what:=ID, LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
LastRow = DataSht.Range("B" & Rows.Count).End(xlUp).Row
NewRow = LastRow
DataRow = NewRow
Else
DataRow = c.Row
End If

'Enter your code here to move data from entry sheet
'to data sheet
DataSht.Range("C" & DataRow) =
EntrySht.Range("E5,I5,M5,C7,I7,M7,C7,C9,F9,I9,M9,E 11,F19,D21,D26,D33,C36")

End Sub


Thank you.

joel
How can I make this macro works
 
There isn't a good shortcut way of moving the data without making the code
hard to understand. I think it is better just to move each piece of data one
at a time. You are moving C7 twice. Is this correct?

Sub GetData()
Set EntrySht = Sheets("Input")
Set DataSht = Sheets("Data")

ID = EntrySht.Range("Q2")

'See if ID already exists
Set c = DataSht.Columns("B").Find(what:=ID, _
LookIn:=xlValues, _
lookat:=xlWhole)
If c Is Nothing Then
'clear Entry Sheet
EntrySht.Range("Q2").ClearContents
Else
'move old data from data sheet to entry sheet
With DataSht
DataRow = c.Row

'to entry sheet
EntrySht.Range("E5") = .Range("A" & DataRow)
EntrySht.Range("I5") = .Range("B" & DataRow)
EntrySht.Range("M5") = .Range("C" & DataRow)
EntrySht.Range("C7") = .Range("D" & DataRow)
EntrySht.Range("I7") = .Range("E" & DataRow)
EntrySht.Range("M7") = .Range("F" & DataRow)
EntrySht.Range("C7") = .Range("G" & DataRow)
EntrySht.Range("C9") = .Range("H" & DataRow)
EntrySht.Range("F9") = .Range("I" & DataRow)
EntrySht.Range("I9") = .Range("J" & DataRow)
EntrySht.Range("M9") = .Range("K" & DataRow)
EntrySht.Range("E11") = .Range("L" & DataRow)
EntrySht.Range("F19") = .Range("M" & DataRow)
EntrySht.Range("D21") = .Range("N" & DataRow)
EntrySht.Range("D26") = .Range("O" & DataRow)
EntrySht.Range("D33") = .Range("P" & DataRow)
EntrySht.Range("D36") = .Range("Q" & DataRow)

End With
End If
End Sub

Sub Submit()
Set EntrySht = Sheets("Input")
Set DataSht = Sheets("Data")

ID = EntrySht.Range("Q2")

'See if ID already exists
Set c = DataSht.Columns("B").Find(what:=ID, LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
LastRow = DataSht.Range("B" & Rows.Count).End(xlUp).Row
NewRow = LastRow
DataRow = NewRow
Else
DataRow = c.Row
End If
With DataSht
.Range("A" & DataRow) = EntrySht.Range("E5")
.Range("B" & DataRow) = EntrySht.Range("I5")
.Range("C" & DataRow) = EntrySht.Range("M5")
.Range("D" & DataRow) = EntrySht.Range("C7")
.Range("E" & DataRow) = EntrySht.Range("I7")
.Range("F" & DataRow) = EntrySht.Range("M7")
.Range("G" & DataRow) = EntrySht.Range("C7")
.Range("H" & DataRow) = EntrySht.Range("C9")
.Range("I" & DataRow) = EntrySht.Range("F9")
.Range("J" & DataRow) = EntrySht.Range("I9")
.Range("K" & DataRow) = EntrySht.Range("M9")
.Range("L" & DataRow) = EntrySht.Range("E11")
.Range("M" & DataRow) = EntrySht.Range("F19")
.Range("N" & DataRow) = EntrySht.Range("D21")
.Range("O" & DataRow) = EntrySht.Range("D26")
.Range("P" & DataRow) = EntrySht.Range("D33")
.Range("Q" & DataRow) = EntrySht.Range("D36")
End With
End Sub




"AnnaC" wrote:

This is a code that Joel so kindly wrote for me, I add some range and delete
some, but now I can not make it work right. I want to update the previous
data using the data entry sheet, but I cant make it write all the data from
the (the cells next to the ID) row to the data entry sheet (Entry) or send
the data back to the data sheet (data). Would somebody mind helping me with
this?

Sub GetData()
Set EntrySht = Sheets("Input")
Set DataSht = Sheets("Data")

ID = EntrySht.Range("Q2")

'See if ID already exists
Set c = DataSht.Columns("B").Find(what:=ID, LookIn:=xlValues,
lookat:=xlWhole)
If c Is Nothing Then
'clear Entry Sheet
EntrySht.Range("Q2").ClearContents
Else
'move old data from data sheet to entry sheet
With DataSht
DataRow = c.Row

'to entry sheet
EntrySht.Range("E5,I5,M5,C7,I7,M7,C7,C9,F9,I9,M9,E 11,F19,D21,D26,D33,C36")
= .Range("C" & c.Row)
End With
End If


End Sub
Sub Submit()
Set EntrySht = Sheets("Input")
Set DataSht = Sheets("Data")

ID = EntrySht.Range("Q2")

'See if ID already exists
Set c = DataSht.Columns("B").Find(what:=ID, LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
LastRow = DataSht.Range("B" & Rows.Count).End(xlUp).Row
NewRow = LastRow
DataRow = NewRow
Else
DataRow = c.Row
End If

'Enter your code here to move data from entry sheet
'to data sheet
DataSht.Range("C" & DataRow) =
EntrySht.Range("E5,I5,M5,C7,I7,M7,C7,C9,F9,I9,M9,E 11,F19,D21,D26,D33,C36")

End Sub


Thank you.

AnnaC
How can I make this macro works
 
Wow! It works perfect!!!!!!!

Thank you for your help!

"joel" wrote:

There isn't a good shortcut way of moving the data without making the code
hard to understand. I think it is better just to move each piece of data one
at a time. You are moving C7 twice. Is this correct?

Sub GetData()
Set EntrySht = Sheets("Input")
Set DataSht = Sheets("Data")

ID = EntrySht.Range("Q2")

'See if ID already exists
Set c = DataSht.Columns("B").Find(what:=ID, _
LookIn:=xlValues, _
lookat:=xlWhole)
If c Is Nothing Then
'clear Entry Sheet
EntrySht.Range("Q2").ClearContents
Else
'move old data from data sheet to entry sheet
With DataSht
DataRow = c.Row

'to entry sheet
EntrySht.Range("E5") = .Range("A" & DataRow)
EntrySht.Range("I5") = .Range("B" & DataRow)
EntrySht.Range("M5") = .Range("C" & DataRow)
EntrySht.Range("C7") = .Range("D" & DataRow)
EntrySht.Range("I7") = .Range("E" & DataRow)
EntrySht.Range("M7") = .Range("F" & DataRow)
EntrySht.Range("C7") = .Range("G" & DataRow)
EntrySht.Range("C9") = .Range("H" & DataRow)
EntrySht.Range("F9") = .Range("I" & DataRow)
EntrySht.Range("I9") = .Range("J" & DataRow)
EntrySht.Range("M9") = .Range("K" & DataRow)
EntrySht.Range("E11") = .Range("L" & DataRow)
EntrySht.Range("F19") = .Range("M" & DataRow)
EntrySht.Range("D21") = .Range("N" & DataRow)
EntrySht.Range("D26") = .Range("O" & DataRow)
EntrySht.Range("D33") = .Range("P" & DataRow)
EntrySht.Range("D36") = .Range("Q" & DataRow)

End With
End If
End Sub

Sub Submit()
Set EntrySht = Sheets("Input")
Set DataSht = Sheets("Data")

ID = EntrySht.Range("Q2")

'See if ID already exists
Set c = DataSht.Columns("B").Find(what:=ID, LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
LastRow = DataSht.Range("B" & Rows.Count).End(xlUp).Row
NewRow = LastRow
DataRow = NewRow
Else
DataRow = c.Row
End If
With DataSht
.Range("A" & DataRow) = EntrySht.Range("E5")
.Range("B" & DataRow) = EntrySht.Range("I5")
.Range("C" & DataRow) = EntrySht.Range("M5")
.Range("D" & DataRow) = EntrySht.Range("C7")
.Range("E" & DataRow) = EntrySht.Range("I7")
.Range("F" & DataRow) = EntrySht.Range("M7")
.Range("G" & DataRow) = EntrySht.Range("C7")
.Range("H" & DataRow) = EntrySht.Range("C9")
.Range("I" & DataRow) = EntrySht.Range("F9")
.Range("J" & DataRow) = EntrySht.Range("I9")
.Range("K" & DataRow) = EntrySht.Range("M9")
.Range("L" & DataRow) = EntrySht.Range("E11")
.Range("M" & DataRow) = EntrySht.Range("F19")
.Range("N" & DataRow) = EntrySht.Range("D21")
.Range("O" & DataRow) = EntrySht.Range("D26")
.Range("P" & DataRow) = EntrySht.Range("D33")
.Range("Q" & DataRow) = EntrySht.Range("D36")
End With
End Sub




"AnnaC" wrote:

This is a code that Joel so kindly wrote for me, I add some range and delete
some, but now I can not make it work right. I want to update the previous
data using the data entry sheet, but I cant make it write all the data from
the (the cells next to the ID) row to the data entry sheet (Entry) or send
the data back to the data sheet (data). Would somebody mind helping me with
this?

Sub GetData()
Set EntrySht = Sheets("Input")
Set DataSht = Sheets("Data")

ID = EntrySht.Range("Q2")

'See if ID already exists
Set c = DataSht.Columns("B").Find(what:=ID, LookIn:=xlValues,
lookat:=xlWhole)
If c Is Nothing Then
'clear Entry Sheet
EntrySht.Range("Q2").ClearContents
Else
'move old data from data sheet to entry sheet
With DataSht
DataRow = c.Row

'to entry sheet
EntrySht.Range("E5,I5,M5,C7,I7,M7,C7,C9,F9,I9,M9,E 11,F19,D21,D26,D33,C36")
= .Range("C" & c.Row)
End With
End If


End Sub
Sub Submit()
Set EntrySht = Sheets("Input")
Set DataSht = Sheets("Data")

ID = EntrySht.Range("Q2")

'See if ID already exists
Set c = DataSht.Columns("B").Find(what:=ID, LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
LastRow = DataSht.Range("B" & Rows.Count).End(xlUp).Row
NewRow = LastRow
DataRow = NewRow
Else
DataRow = c.Row
End If

'Enter your code here to move data from entry sheet
'to data sheet
DataSht.Range("C" & DataRow) =
EntrySht.Range("E5,I5,M5,C7,I7,M7,C7,C9,F9,I9,M9,E 11,F19,D21,D26,D33,C36")

End Sub


Thank you.


All times are GMT +1. The time now is 03:36 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com