Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > How can I make this macro works
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default 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.


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default 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.


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default 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.


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Make a product that works msnyc07 Excel Worksheet Functions 12 February 11th 10 01:34 AM
Excel 2002: How to make the formula works ? Mr. Low Excel Discussion (Misc queries) 8 June 18th 07 01:57 PM
How to make a null cell as 0 (Zero) so that the subtraction works Kekin Kakka Excel Worksheet Functions 6 May 9th 06 08:24 PM
Make table query works intermittently quartz[_2_] Excel Programming 2 September 14th 05 09:16 PM
I would like to make a schedule for my dept. Who works what days . Chiki Charts and Charting in Excel 0 December 6th 04 01:55 AM


All times are GMT +1. The time now is 06:44 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement